T-SQL Conditional Select Across Tables

Question

I have a table (lets call it main_guys) that looks like:

id, other_item_id, other_item_type, another_column, group_id

The other_item_type tells me which additional table holds additional information identified by the other_item_id.

Additional columns required in the query depends on the table identified by other_item_type. So for other_item_type == 'Type-X' I need to get values from columns foo and bar from the X table. While for other_item_type == Type-Y I need to get values from columns ick and blah from Y table.

In a perfect world, I would be able to get something like:

id, other_item_id, other_item_type, another_column, group_id, foo, bar, ick, blah - with values filled in where needed per type and other columns being null or what not if not needed.

One additional issue is that other_item_type and other_item_id can also be empty - and the rest of the columns in that row need to be returned in the select - but in the empty case none of the additional columns across the other tables should contain any values.

In terms of pseudo-code...

// obviously not proper code - just trying to communicate need
// Note: Need all items from main_guys table - 
// even if main_guys.other_item_type is empty
select * from main_guys where main_guys.group_id == "12345" 
if main_guys.other_item_type == "Type-X"
 select x.foo, x.bar from x where x.id == main_guys.other_item_id
else if main_guys.other_item_type == "Type-Y" 
 select y.ick, y.bar from y where y.id == main_guys.other_item_id

Thanks for any help or suggestions.

Answer 1

You need to look at LEFT OUTER JOINs

select * from main_guys MG
    left outer join X 
       on MG.id=X.other_item_id and other_item_type='Type-X'

    left outer join Y 
       on MG.id=Y.other_item_id and other_item_type='Type-Y'
    where MG.group_id = '12345' --not sure why this is text

Answer 2

Do you mean something like this

SELECT mg.id, mg.other_item_id, mg.other_item_type, coalesce(x.ick, y.ick)
FROM main_guys mg
     LEFT OUTER JOIN x ON x.id = mg.Other_Item_ID AND mg.Other_Item_Type = 'Type-X'
     LEFT OUTER JOIN y ON y.id = mg.Other_Item_ID AND mg.Other_Item_Type = 'Type-Y'

If I understand you correctly it would mean you have a reference to two (or more) tables in one column of a master table. I would encourage you to change the design of the master table to have one column having one meaning.

Answer 3

Hi,

You could do something like this:

SELECT m.*, x.*, y.*
FROM main_guys m
    LEFT JOIN x ON m.id=x.other_item_id AND m.other_item_type = 'Type-X'
    LEFT JOIN y ON m.id=y.other_item_id AND m.other_item_type = 'Type-Y'

Answer 4

My approach would be create set on unions with hardcoded other_item_type values as you have

select * from 
(
    select main.*, x.foo as col1, x.bar as col2
    from main_guys as main
    inner join x on x.id == main_guys.other_item_id
    where other_item_type == "Type-X"
    union
    select main.*, y.ick as col1, y.bar as col2
    from main_guys as main
    inner join y on y.id == main_guys.other_item_id
    where other_item_type == "Type-Y" 
) as a

It depends how many distinct values of other_item_type you have and how many want to handle.