sql - Select Null From A Table When Records May Not Exist -

sql - Select Null From A Table When Records May Not Exist -

i have largish( 10's of millions of rows) sql table, lists attribute types, , attributes. want investigate relationship between subsets (three or 4 @ time) of these attributes, given object. objects may have some, all, or none, of attributes i'm interested in. if has none of attributes i'm interested in, can consider not exist.

id | attributetype | attributevalue ------------------------------------ 01 | 01 | 100 01 | 02 | 4500 01 | 04 | d 01 | 15 | e

the problem is, want homecoming results attributes types i'm in if of them exist, no result if none of them do.

so performing query:

select case when att1.id null att2.id else att1.id end id, att1.attributevalue attribute5, att2.attributevalue attribute6 attributes att1 total outer bring together attributes att2 on att1.id = att2.id , att1.attributetype = 5 , att2.attributetype = 6

doesn't work id 1 because has no attribute of either type, query create null records on either side of join, see this:

id | attribute5 | attribute6 ----------------------------- 01 | 100 | null 01 | 4500 | null

if seek avoid creating null records miss out on records want see. query:

select case when att1.id null att2.id else att1.id end id, att1.attributevalue attribute1, att2.attributevalue attribute2 attributes att1 total outer bring together attributes att2 on att1.id = att2.id att1.attributetype = 1 , att2.attributetype = 3

produces nothing, should produce:

id | attribute1 | attribute3 ----------------------------- 01 | 100 | null

i can prepare both of these problems using left join,

select case when att1.id null att2.id else att1.id end id, att1.attributevalue attribute1, att2.attributevalue attribute3 attributes att1 left bring together attributes att2 on att1.id = att2.id , att2.attributetype = 3 att1.attributetype = 1

produces right output.

the problem doesn't treat attributes equally. so, if id 01 has value attribute 01 not 03 it's fine, if didn't have 01, , did have 03, wouldn't see it. becomes more of problem when extend 3 , 4 joins.

ideally, given how i'm going have run query different attribute types, , how much processing time goes creating attribute table in first place, i'd love able results need, , no others, single query.

try this...

select distinct base.id, att1.attributevalue attribute1, att2.attributevalue attribute2, att3.attributevalue attribute3, att4.attributevalue attribute4 attributes base of operations left bring together attributes att1 on base.id = att1.id , att1.attributetype = 1 left bring together attributes att2 on base.id = att2.id , att2.attributetype = 2 left bring together attributes att3 on base.id = att3.id , att3.attributetype = 3 left bring together attributes att4 on base.id = att4.id , att4.attributetype = 4 base.id = 1

you need "static" table, , left bring together attributes on that...

ideally, since you're not using id base of operations table, improve performing if didn't utilize entire table here, but, given layout, work, if example. if know you're looking @ id 1,3,5,7, improve set them in variable/temp table, , bring together off eliminate having bring together attributes table time.

sql sql-server join