CODEWITHSUNDEEP
sqlselectjoinunion
Mahdi
Mahdi

Reputation: 9417

Simple but nested SELECT query

I have four tables a,b,c,d. In table a I have pair of id, name. In table b I have pair of idx, idy. The b.idy comes from table a.id. In tables c, d I have pair of id, value which are related to table b.idx.

I have to perform a query like this:

SELECT c.value, d.value 
FROM a,b,c,d
WHERE a.name = "test" AND b.idy = a.id AND (c.id = b.idx AND d.id = b.idx)

The problem is sometimes there are missing records in tables c, d so AND will return zero records, but I need to have results if there are available either in c or d. Also I can't use OR because it returns other rows also for both tables.

I guess there would be a solution using UNION or even just nested SELECTs. I prefer to do not use JOIN or using separated quires.

Thanks in advance!

Update:

The reason for avoiding using JOIN is performance. The structure that I'm working on now is much more complex rather than this one, so I'm sure with JOIN I would have serious performance issues in the upcoming future.

Upvotes: 0

Views: 521

Answers (3)

JosephH
JosephH

Reputation: 8815

SELECT c.value, d.value 
FROM a,b,c,d
WHERE a.name = "test" AND b.idy = a.id AND (c.id = b.idx AND d.id = b.idx)
UNION
SELECT c.value, NULL
FROM a,b,c
WHERE a.name = "test" AND b.idy = a.id AND c.id = b.idx AND NOT EXISTS( SELECT NULL FROM d where d.id = b.idx )
UNION
SELECT NULL, d.value
FROM a,b,d
WHERE a.name = "test" AND b.idy = a.id AND d.id = b.idx AND NOT EXISTS( SELECT NULL FROM c where c.id = b.idx )

Upvotes: 1

Taryn
Taryn

Reputation: 247670

I think you want to use a LEFT JOIN:

SELECT c.value, d.value 
FROM a
LEFT JOIN b
    ON a.id = b.idy
LEFT JOIN c
    ON b.idx = c.id
LEFT JOIN d
    ON b.idx = d.id
WHERE a.name = "test"

Using the comma join syntax is an INNER JOIN which requires that the records be available in all tables.

If you need help in reviewing JOIN syntax there is a helpful guide:

A Visual Explanation of SQL Joins

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

select c.value, d.value
from a
inner join b on b.idy = a.id
left outer join c on c.id = b.idx
left outer join d on d.id = b.idx
where a.name = "test" 
    and coalesce(c.id, d.id) is not null

Upvotes: 1

Related Questions