My tables are set up 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 (table1.id), if table2.t3_id exists in table 3 (table3.id).
The important point is that there may exist multiple rows with same t3_id in table 2. So, the foreign key relation between table 2 and table 3 is not unique and can be also 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
But this statement returns multiple rows. - But I only need one. How I can achieve this?
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
)
)