Some SQL query issues.

[joebloggs2004]

I have some SQL query problems, which I can't figure out exactly where the problem is.

SELECT RL.productNum, RL.qtyRequested, PL.quantityPicked, (RLqtyRequested – PL.quantityPicked)
FROM REQUEST_LIST AS RL, PICKING_LIST AS PL
WHERE RL.productNum = (SELECT productNum FROM PICKING_LIST);

I think its some problem with the ( ) part on the SELECT line, but what exactly I have no idea.

SELECT PL.productNum, P.description, SUM(PL.quantity)
FROM PRODUCT AS P, PROD_LOCATION AS PL
WHERE P.productNum = PL.productNum
GROUP BY PL.productNum;

I keep getting this message: "You tried to execute a query that does not include the specified expression 'description' as part of an aggregate function." but I don't understand what it means. Can anyone explain what it means and how/where to fix the problem? I think the problem is with GROUP BY, but I'm unsure.

And this problem:
List supervisors (staffid, surname and first name) and all of their subordinates (staffid, surname and first name).
I can't figure out how to get it to display the supervisors then the subordinates of that supervisor. It doesn't make a lot of sense. The supervisors have a supervisor attribute equalling null (or nothing).

 

[ecniv]

SELECT PL.productNum, P.description, SUM(PL.quantity) 
FROM PRODUCT AS P, PROD_LOCATION AS PL 
WHERE P.productNum = PL.productNum 
GROUP BY PL.productNum[b], P.description[/b];

The easy one, this is because when you use a group bby you must always list all the fields that are using aggregate functions.

SELECT RL.productNum, RL.qtyRequested, PL.quantityPicked, (RLqtyRequested – PL.quantityPicked) 
FROM REQUEST_LIST AS RL, PICKING_LIST AS PL 
WHERE RL.productNum = pl.productnum;

A join I think... Since you haven't specifed the db, this may work...

Left join

SELECT RL.productNum, RL.qtyRequested, PL.quantityPicked, (RLqtyRequested – PL.quantityPicked) 
FROM (REQUEST_LIST AS RL) left join (PICKING_LIST AS PL) on rl.productnum=pl.productnum

You can add a where clause to filter non links off :

Where not pl.productnum is null

And lastly - to get supervisors only

where [fieldsupervisor] is null

Change to the following to get underlings

Where not [fie...

Or add to the bottom of the sql statement

Order By [fieldsupervisor]

Vince

 

[joebloggs2004]

Whats wrong with this statement??

SELECT P.productNum, P.description, PL.locationNum, PL.quantityOnHand
FROM PRODUCT AS P, PROD_LOCATION AS PL
WHERE PL.quantityOnHand > 0
ORDER BY P.productNum, PL.warehouseNum, PL.locationNum
GROUP BY P.productNum, P.description, PL.locationNum, PL.quantityOnHand;

I've tried a few different variations and I keep getting cartesian product.
What it has to do is get the product number, description, location number and the quantity on hand from the tables PRODUCT and PROD_LOCATION (as you can see above) then sort the list by by product number and place all locations (warehouse number and location number) for a given product together.
I'm still stuck on the ORDER BY/GROUP BY part. I don't quite understand how that works. Would appreciate it if you could explain in a little more detail what an aggregate function is, and how that would apply to this query. Thanks