2 replies to this topic

[Lokantis]

So I have the following tables:

Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)

Product (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)

OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, ShpName, ShpStreet, ShpCity, ShpState, ShpZip)

OrdProd (OrdNo, ProdNo, Qty)

I am trying to do a query as follows:
List all the products (number, name, manufacturer and price) that are never ordered by the 'CO' state customers.

The closest thing that I got was:

SELECT DISTINCT ProdNo, ProdName, ProdMfg, ProdPrice
FROM Product
INNER JOIN OrdProd
ON Product.ProdNo = OrdProd.ProdNo
INNER JOIN OrderTbl
ON OrdProd.OrdNo = OrderTbl.OrdNo
INNER JOIN Customer
ON OrderTbl.CustNo = Customer.CustNo
WHERE ShpState <> 'CO';

I know thats wrong of course. But can someone get me on the right track, I am really confused.

[A319]

Firstly theres the maintable from there you are selecting the other information (Product). But I want to know what is included in the other tables and by witch columns it's linked.

MfG

[WingedPanther]

SELECT DISTINCT ProdNo, ProdName, ProdMfg, ProdPrice
FROM Product
INNER JOIN OrdProd
ON Product.ProdNo = OrdProd.ProdNo
LEFT OUTER JOIN OrderTbl
ON OrdProd.OrdNo = OrderTbl.OrdNo AND OrderTbl.ShpState='CO'
INNER JOIN Customer
ON OrderTbl.CustNo = Customer.CustNo
WHERE ShpState IS NULL;