Shpanders Shpanders - 1 year ago 90
MySQL Question

SQL Select columns from 2 tables with foreign keys in another table

Given database schema:

Part( P#, PName, Producer, Year, Price)

Customer( C#, CName, Province)

Supply(S#, P#, C#, Quantity, Amount, Date)

I need to create this query:

For each supply with a quantity over 1000 and after 2015-01-01, list the part
name, customer name and customer province.

Here's what I have but obviously it's incorrect:

SELECT PName FROM Part AND CName, Province FROM Customer
WHERE Part.P#= Supply=P#, Customer.C# = Supply.C# IN (SELECT * FROM Supply
WHERE Quantity > 1000 AND Date > 2015-01-01)

Answer Source

The query you want should be:

SELECT p.PName, c.CName, c.Province
FROM Supply s
JOIN Customer c
ON c.C# = s.C#
JOIN Part p
ON p.P# = s.P#
WHERE c.Quantity > 1000 AND c.Date > '2015-01-01'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download