CODEWITHSUNDEEP
sqloracle-database
mrfred
mrfred

Reputation: 631

Oracle SQL select null on multiple of the same table

I'm pretty new to sql and I've been trying something with very little luck. Here's my situation. I have tableA that has multiple links to another tableB:

select * from tableA where id = 1;
+--+---------+---------+---------+
|ID|TABLEB1ID|TABLEB2ID|TABLEB3ID|
+--+---------+---------+---------+
|1 |1        |2        |3        |
+--+---------+---------+---------+

select * from tableB;
+--+----+
|ID|NAME|
+--+----+
|1 |A   |
+--+----+
|2 |B   |
+--+----+
|3 |C   |
+--+----+

Now, I want to get values from each of the rows corresponding to those 3 ids. Right now I'm doing something like this:

select tableA.*, tableB1.name name1, tableB2.name name2, tableB3.name name3
from tableA, tableB tableB1, tableB tableB2, tableB tableB3
where tableA.id = 1 and
      tableA.tableB1id = tableB1.id and
      tableA.tableB2id = tableB2.id and
      tableA.tableB3id = tableB3.id;
+--+---------+---------+---------+-----+-----+-----+
|ID|TABLEB1ID|TABLEB2ID|TABLEB3ID|NAME1|NAME2|NAME3|
+--+---------+---------+---------+-----+-----+-----+
|1 |1        |2        |3        |A    |B    |C    |
+--+---------+---------+---------+-----+-----+-----+

Now this works great except that when one of these ids is null, I get no results:

select * from tableA where id = 2;
+--+---------+---------+---------+
|ID|TABLEB1ID|TABLEB2ID|TABLEB3ID|
+--+---------+---------+---------+
|2 |1        |2        |         |
+--+---------+---------+---------+

select tableA.*, tableB1.name name1, tableB2.name name2, tableB3.name name3
from tableA, tableB tableB1, tableB tableB2, tableB tableB3
where tableA.id = 2 and
      tableA.tableB1id = tableB1.id and
      tableA.tableB2id = tableB2.id and
      tableA.tableB3id = tableB3.id;

+--+---------+---------+---------+-----+-----+-----+
|ID|TABLEB1ID|TABLEB2ID|TABLEB3ID|NAME1|NAME2|NAME3|
+--+---------+---------+---------+-----+-----+-----+
+--+---------+---------+---------+-----+-----+-----+

I'm not too familiar with all the functionality that SQL has, and I would appreciate any help in getting the output like the following:

+--+---------+---------+---------+-----+-----+-----+
|ID|TABLEB1ID|TABLEB2ID|TABLEB3ID|NAME1|NAME2|NAME3|
+--+---------+---------+---------+-----+-----+-----+
|2 |1        |2        |         |A    |B    |     |
+--+---------+---------+---------+-----+-----+-----+

OR

+--+---------+---------+-----+-----+
|ID|TABLEB1ID|TABLEB2ID|NAME1|NAME2|
+--+---------+---------+-----+-----+
|2 |1        |2        |A    |B    |
+--+---------+---------+-----+-----+

I know there has to be a function that can help me with this, I'm just having trouble finding that, and writing the query. Any help would be great. Thanks.

Upvotes: 2

Views: 108

Answers (2)

Boneist
Boneist

Reputation: 23588

An alternative to the left outer join would be to use subqueries in the select list:

select t1.*,
       (select t2.name from tableb t2 where t1.tableb1id = t2.id) name1,
       (select t3.name from tableb t3 where t1.tableb2id = t3.id) name2,
       (select t4.name from tableb t4 where t1.tableb3id = t4.id) name3
from   tableA t1;

        ID  TABLEB1ID  TABLEB2ID  TABLEB3ID NAME1 NAME2 NAME3
---------- ---------- ---------- ---------- ----- ----- -----
         1          1          2          3 A     B     C    
         2          1          2            A     B

This has the benefit of enabling subquery caching, which may or may not provide performance benefits depending on whether your data has many repeat values or not. You should test both queries against your data to see which one performs best for you.

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You need to use a LEFT OUTER JOIN for this approach. The type of JOIN you're doing right now is the equivalent of an INNER JOIN, which won't return unless both sides of all joins are satisfied, which is why you aren't getting any results.

With an OUTER JOIN, if the data does not exist for any of the TableB variants, you will simply get back NULL.

Try the following instead:

Select      A.*, 
            B1.Name Name1,
            B2.Name Name2,
            B3.Name Name3
From        TableA  A
Left Join   TableB  B1  On  A.TableB1ID = B1.Id
Left Join   TableB  B2  On  A.TableB2ID = B2.Id
Left Join   TableB  B3  On  A.TableB3ID = B3.Id
Where       A.Id = 2;

Upvotes: 3

Related Questions