Oracle SQL Query with joining four tables to get only one row of multiple entries

Question

My tables are set up something like this:

Table 1            Table 2                            Table 3        
+-------+-----+    +-------+-------+-------+-----+    +-------+-----+
| ID    | ... |    | ID    | T1_ID | T3_ID | ... |    | ID    | ... |
+-------+-----+    +-------+-------|-------|-----|    |-------|-----|
|  101  | ... |    |  202  |  101  |  301  | ... |    |  300  | ... |
|  102  | ... |    |  203  |  101  |  302  | ... |    |  302  | ... |
|  104  | ... |    |  204  |  101  |  302  | ... |    |  314  | ... |
+-------+-----+    |  205  |  101  |  302  | ... |    +-------+-----+
                   |  206  |  104  |  327  | ... |                   
                   +-------+-------+-------+-----+

I want to construct a subquery statement that will select only one row of table 2 for an given id of table 1, if table2.t3_id exists in table 3.

The important point is that there maybe exist multiple rows with same t3_id in table 2. So, the foreign key relation between table 2 and table 3 is not unique or does not exist at all.

My idea was the following statement:

inner join 
(
    SELECT * 
    FROM (
        SELECT t3_id, t1_id, id 
        FROM table2 
        WHERE EXISTS 
        (
            SELECT id 
            FROM table3
        )
    ) 
    WHERE ROWNUM=1
) tb2 ON tb1.id = tb2.t1_id

This statement returns multiple rows, but I only need one. How do I do this?

And the result should be? And if multiple rows exist, which one to take? — juergen d, May 23 '15 at 13:04
If multiple rows exist, it does not matter which one to take. You could take the first one. The result should be the id from table 2 or its row for join. — flyaround, May 23 '15 at 13:08
What columns do you actually need? Is there a reason you can't do something like `SELECT t1_id, MAX(t3_id) FROM Table2 JOIN Table3 ON Table3.id = Table2.t3_id GROUP BY t1_id`? Why do you have missing values in `Table3` anyways? Having null partial keys is usually a bad idea (something appears to be screwy with you db/application design, as anonymized). — Clockwork-Muse, May 23 '15 at 14:40
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Sep 11 '22 at 13:06

score 2 · Accepted Answer · answered May 23 '15 at 15:28

Not tested but should do what you need

SELECT *
FROM table1 t1 JOIN table2 t2 
ON ( t1.id = t2.t1_id
     AND EXISTS ( SELECT 'x'
                  FROM table3 t3
                  WHERE t2.t3_id = t3.id
                 )
     AND NOT EXISTS ( SELECT 'a'
                      FROM table2 t22 
                      WHERE t22.t1_id = t2.t1_id
                      AND t22.id < t2.id
                     )
   )

Gordon Linoff · Answer 2 · 2015-05-23T14:53:54.277

0

You can get one row of multiple entries by using row_number() to enumerate them and then selecting just one value. Here is an example:

select . . .
from table1 t1 join
     (select t2.*, row_number() over (partition by t.id order by t2.id) as seqnum
      from table2 t2
     ) t2
     on t2.t1_id = t1.id and t2.seqnum = 1;

EDIT:

For all three tables, you want to do the row_number() all the joins:

select . . .
from (select . . ., row_number() over (partition by t1_id order by id) as seqnum
      from table1 t1 join
           table2 t2
           on t2.t1_id = t1.id join
           table3 t3
           on t2.t3_id = t3.id
     ) t
where seqnum = 1;

edited May 23 '15 at 14:53

answered May 23 '15 at 13:53

Gordon Linoff

1,242,037
58
646
786

Would doing it over all the relations impose a greater penalty than moving the join to `Table1` up a level? `t1_id` is going to be uniqued because it's the partition, so... – Clockwork-Muse May 23 '15 at 14:42

Oracle SQL Query with joining four tables to get only one row of multiple entries

2 Answers2