how to inner join base on the condition | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to inner join base on the condition

got a question here three tables A, B, C base on the one field in Table A,
table A will inner Join the table B or Table C, any idea ? thanks
not sure if i am missing the point of your question, but i think we need a bit more info on what you are trying to do!
Hi Frank, it sounds like you have a subtype/supertype relationship. In which case you’ll either need a union or (if your only after retrieving a single field out of the subtype tables then a case statement for that field) select *
from tablea
inner join tableb
union all
select *
from tablea
inner join tablec or select *,
case type when ‘b’ then (select col1 from tableb where col2 = tablea.col2 )
when ‘c’ then (select col1 from tablec where col2 = tablea.col2 ) end
from tablea Cheers
Twan
thanks on this select *,
case type when ‘b’ then (select col1 from tableb where col2 = tablea.col2 )
when ‘c’ then (select col1 from tablec where col2 = tablea.col2 ) end
from tablea if i need more than one column from tableb or c , is there any other solutions without the "UNION" select *
from tablea
inner join tableb
union all
select *
from tablea
inner join tablec Thanks

I don’t think you can then get around doing a union unless you add a case for each column, but that’s likely to be bad for performance you could possibly union the tableb and tablec records (along with the type value for each table) and then join the result of that with tablea…? select *
from tablea
inner join
( select ‘b’ as type, * from tableb
union all
select ‘c’, * from tablec ) tableboth
on tablea.id = tableboth.id
and tablea.type = tableboth.type Cheers
Twan
Wouldn’t something a little more traditional be better here? Not sure why you need to union the two join tables….<br /><br /><br />select *<br />from tablea<br /> inner join tableb<br /> on tablea.joinfield=tableb.joinfield<br /> and tablea.typefield = ‘valueforb'<br /> inner join tablec<br /> on tablea.joinfield=tablec.joinfield<br /> and tablea.typefield = ‘valueforc'<br /><br /><br />……of course, I’ve probably completely misunderstood…..being only a junior <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Thanks a lot
Hi ya, you couldn’t do an inner join with both tables since tablea.typefield only has one value for a single row, so the query you have would not return any results. However it has prompted me to thin that you could do a left outer join with both tables and then use a case statement for each of the columns, that may be more efficient than a union all Cheers
Twan
]]>