First query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

First query help

Author

Topic

mmani2334
Starting Member

2 Posts

Posted - 2015-02-04 : 18:18:28

I am trying to write my first query for the statement below and I stuck. Any help would be greatly appreciated.

I believe I would have to use a join for the three tables this query would have to look at?

1. Get full part-details of all parts that are shipped to any project in LONDON.

SupplierNo Name Status City
S1 SMITH 20 LONDON
S2 JONES 10 PARIS
S3 BLAKE 30 PARIS
S4 CLARK 20 LONDON
S5 ADAMS 30 ATHENS

PartNo Name Color Weight City
P1 NUT RED 12 LONDON
P2 BOLT GREEN 17 PARIS
P3 SCREW BLUE 17 ROME
P4 SCREW RED 14 LONDON
P5 CAMERA BLUE 32 PARIS
P6 WRENCH RED 19 LONDON
P7 C-O-G GREEN 12 ROME

ProjectNo Name City
J1 SORTER PARIS
J2 PUNCH ROME
J3 READER ATHENS
J4 CONSOLE ATHENS
J5 COLLATOR LONDON
J6 TERMINAL OSLO
J7 TAPE LONDON
J8 DRUM LONDON

SupplierNo PartNo ProjectNo Quantity
S1 P1 J1 200
S1 P1 J4 700
S1 P3 J1 450
S1 P3 J2 210
S1 P3 J3 700
S2 P3 J4 509
S2 P3 J5 600
S2 P3 J6 400
S2 P3 J7 812
S3 P5 J6 750
S3 P3 J2 215
S3 P4 J1 512
S3 P6 J2 313
S4 P6 J3 314
S4 P2 J6 250
S4 P5 J5 179
S4 P5 J2 513
S5 P7 J4 145
S5 P1 J5 269
S5 P3 J7 874
S5 P4 J4 476
S5 P5 J4 529
S5 P6 J4 318
S5 P2 J4 619

Thank you! javascript:insertsmilie('

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-05 : 07:32:51

first you need to filter your projects by city London:

select
*
from projects_table as prj
where prj.city = 'LONDON'

after that you can use the quantity table to make the rations between the tables

select
*
from projects_table as prj
LEFT JOIN quantity_table as qtt
on prj.ProjectNo = qtt.ProjectNo
where prj.city = 'LONDON'

Now, using the quantity table you can create the relation with the others tables

select
*
from projects_table as prj
LEFT JOIN quantity_table as qtt
on prj.ProjectNo = qtt.ProjectNo
LEFT JOIN supllier_table sup
on qtt.SupplierNo = sup.SupplierNo
where prj.city = 'LONDON'

If you understand the logic i think you can create alone the relation with the part_table.

------------------------
PS - Sorry my bad english

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources