CODEWITHSUNDEEP
sqlselectjoin
Deepak Kumar
Deepak Kumar

Reputation: 55

inner join table2 OR table 3

I have three tables table1, table2, table3

table1 and table2 are main tables with almost same structure

table3 is linked with both tables, half record depends on table1 and half on table2

I want something like this

select a.column1,a.column2, c.column1
from table3 c
     inner join table1 a ON a.pkey = c.fkey
  OR inner join table2 a ON a.pkey = c.fkey

Upvotes: 1

Views: 279

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

I would suggest using left join, if you care about performance:

select coalesce(t1.column1, t2.column1) as column1,
       coalesce(t1.column2, t2.column2) as column2,
       c.column1
from table3 c left join
     table1 t1
     on t1.pkey = c.fkey left join
     table2 t2
     on t2.pkey = c.fkey
where t1.pkey is not null or t2.pkey is not null;

This can take advantage of indexes on table1(pkey, column1, column2) and table2(pkey, column1, column2).

Upvotes: 1

Mureinik
Mureinik

Reputation: 311998

You could use union all to emulate this behavior:

SELECT a.column1, a.column2, c.column1
FROM  table3 c
INNER JOIN (SELECT column1, fkey
            FROM   table1
            UNION ALL
            SELECT column1, fkey
            FROM   table2) a ON a.pkey = c.fkey

Upvotes: 1

Related Questions