SQL Staement: Help wanted

Hi guys,

I wonder if you could help me. Below are a few tables:-

Table Name: s

sno sname Status City
-------------------------------------------
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens


Table Name: p

pno pname colour City
-------------------------------------------
p1 Nut Red London
p2 Bolt Green Paris
p3 Screw Blue Paris
p4 Screw Red London
p5 Cam Blue Athens
p6 Cog Red London

Table Name: sp

sno pno qty
----------------------
S1 p1 300
S1 p2 200
S1 p3 100
S1 p4 400
S2 p1 300
S2 p1 300
S2 p2 200
S2 p3 100
S2 p4 400

Could you write a sql statement that list distinct pairs of pno’s which are co-located i.e are located in the same city?

Thanks,

Teakster

This should give you all records. Do you only want city's that match from p and s tables or what?


select count(*) as cnt,s.sno,s.sname,s.status,s.city,p.pno p.pname p.colour p.City as pcity,sp.qty
from p left outer join sp on p.pno = sp.pno left outer join s on s.sno = sp.sno group by s.sno,s.sname,s.status,s.city,p.pno p.pname p.colour p.City as pcity,sp.qty