Brad Lamitie Brad Lamitie - 2 months ago 7
SQL Question

Collapsing a 3 tier subquery to a Join statement

I seem to be having an issue collapsing my 3 tier subquery to a join statement.
Heres the code :

Select pid
from orders
where aid in (
select aid
from orders
WHERE cid IN (
select cid
from customers
where city = 'Kyoto'
)
);


The Database im currently working with

EDIT: I am trying to show the ids of products ordered through any agent who makes at least one order for a customer in Kyoto

Answer

Try this:

Select o.pid 
from orders o 
inner join orders oo on oo.aid = o.aid
inner join customers c on c.cid = oo.cid and c.city = 'Kyoto'

I think that the aliases (o, oo and c) are necessary to make it work as expected