Brad Lamitie Brad Lamitie - 1 year ago 86
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 Source

Try this:

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download