CODEWITHSUNDEEP
sqloracle-databasejoinleft-join
RNJ
RNJ

Reputation: 15552

Multiple join not joining as expected

I have the following tables

table 2 with a fk to table 1
table 3 with a fk to table 1

In table 2 I have two rows linked to table 1 In table 3 I have one row linked table 1

I am trying to produce a table that has

| table1 pk  | table 2 pk | null |
| table1 pk  | table 2 pk | null |
| table1 pk  | null | table 3 pk |

However when I try the following I get

select tab1.id, tab2.id, tab3.id
from table1 tab1 
left join tab2 on tab1.id = tab2.tab1_id
left join tab3 on tab1.id = tab3.tab1_id

gives this table 

| table1 pk  | table 2 pk | table 3 pk |
| table1 pk  | table 2 pk | table 3 pk |

Can anyone help with this SQL please?

Thanks in advance

EDIT

I think I may have simplified this a bit too much. Ideally output would be 

| table1 pk  | table 2 pk |
| table1 pk  | table 3 pk |
| table1 pk  | table 3 pk |

Once I get this join working it will be added to another massive query...

Upvotes: 1

Views: 101

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146409

One unusual solution is 

select coalesce(t2.tab1_id, t3.tab1_id) pk,
   t2.pk, t3.pk
from table2 t2 
   full join table3 t3
       on t3.tab1_id = t2.tab1_id
where exists (select * from table1
              where pk in (t2.tab1_id, t3.tab1_id)

NOTE (Edit) As noted by Andriy M in comment, the where clause only eliminates rows from table2 and table3 where the FK does not exist in table1, which cannot exist if FK constraints have been properly applied to table2 and table3.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77657

You could use a join to the result of a union:

SELECT
  t1.table1pk,
  t23.table2pk,
  t23.table3pk
FROM table1 t1
INNER JOIN
(
  SELECT table2pk, NULL AS table3pk, table1fk
  FROM table2

  UNION ALL

  SELECT NULL AS table2pk, table3pk, table1fk
  FROM table3
) t23
ON t1.table1pk = t23.table1fk
;

Or you could use a union of two joins' results:

SELECT
  t1.table1pk,
  t2.table2pk,
  NULL AS table3pk
FROM table1 t1
INNER JOIN table2 t2
ON t1.table1pk = t2.table1fk

UNION ALL

SELECT
  t1.table1pk,
  NULL AS table2pk,
  t3.table3pk
FROM table1 t1
INNER JOIN table3 t3
ON t1.table1pk = t3.table1fk
;

Both methods could be adapted to produce the two-column version of the desired output:

  • a join to a union:

    SELECT
  t1.table1pk,
  t23.otherpk
FROM table1 t1
INNER JOIN
(
  SELECT table2pk AS otherpk, table1fk
  FROM table2

  UNION ALL

  SELECT table3pk AS otherpk, table1fk
  FROM table3
) t23
ON t1.table1pk = t23.table1fk
;

  • a union of joins:

    SELECT
  t1.table1pk,
  t2.table2pk AS otherpk
FROM table1 t1
INNER JOIN table2 t2
ON t1.table1pk = t2.table1fk

UNION ALL

SELECT
  t1.table1pk,
  t3.table3pk AS otherpk
FROM table1 t1
INNER JOIN table3 t3
ON t1.table1pk = t3.table1fk
;

Upvotes: 1

Srini V
Srini V

Reputation: 11355

I don't see anything crazy here

WITH TAB1
    AS (SELECT 1 AS ID FROM DUAL
        UNION ALL
        SELECT 2 AS ID FROM DUAL
        UNION ALL
        SELECT 3 AS ID FROM DUAL),
    TAB2
    AS (SELECT 1 AS TAB1_ID, 'A' AS ID FROM DUAL
        UNION ALL
        SELECT 2 AS TAB1_ID, 'B' AS ID
        FROM DUAL),
    TAB3
    AS (SELECT 3 AS TAB1_ID, 'C' AS ID
        FROM DUAL)
SELECT
      TAB1.ID,
      COALESCE ( TAB2.ID,
               TAB3.ID )
FROM
      TAB1
      LEFT JOIN TAB2
          ON TAB1.ID = TAB2.TAB1_ID
      LEFT JOIN TAB3
          ON TAB1.ID = TAB3.TAB1_ID;

1   A   
2   B   
3   C

Upvotes: 1

Related Questions