Using a table to loop from a SQL Query

Question

I have a table like this

SparePart

sparePartID	name	price	modelID
12V	Preheat Start Relay	1470	WADR40E
12V.	Instrument Assembly	1290	WADR40E
12V..	Motor Module(360)	17810	WADR40E
40CC	HST Assembly	264840	WADR40E
4L88	Oil Filter Core	1200	WADR40E
4L88.	Diesel oil Filter Core	260	WADR40E
4SB1490	Belt	9930	WADR40E
50*2.65	Axeal -O Ring	80	WADR40E
608510	Oil seal	1180	WADR40E
9J-5-1605	Joint Belt	8960	WADR40E

and MainStock

originalQty	qty	shipmentID	sparePartID
20	20	RnsttFOY	RT125-03001
10	10	SHPMT78	RT125-03001
8	8	RH987ho	12V
0	0	RH987ho	4SB1490

So I use a query like this to count all stock from all shipments

SELECT SUM(`qty`) FROM MainStock WHERE sparePartID='RT125-03001';

I want to loop through each of sparePartID from SparePart table and get table results. I tried something like this.

SELECT SUM(`qty`)
    -> FROM MainStock, SparePart
    -> WHERE sparePartID=SparePart.sparePartID;

But I get a message saying

ERROR 1052 (23000): Column 'sparePartID' in where clause is ambiguous

So how can I accomplish this?

Do you want to list up all the sparePartID with its given sum('qty')? — Moptan
– Moptan, Commented Aug 24, 2016 at 12:54
The reason you're getting that message is because you aren't telling the query which table sparePartID is coming from (change it to MainStock.SparePartID = SparePart.sparePartID). But that doesn't help you get to your expected result. Can you show us what you expect the resultset to look like? — Siyual
– Siyual, Commented Aug 24, 2016 at 12:55

Lamak · Accepted Answer · 2016-08-24 12:56:21Z

3

You really don't need a loop, just a simple join and GROUP BY:

SELECT SP.sparePartID, SUM(qty) as qty
FROM SparePart SP
LEFT JOIN MainStock MS
    ON SP.sparePartID = MS.sparePartID
GROUP BY SP.sparePartID

answered Aug 24, 2016 at 12:56

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2016-08-24 12:54:40Z

1

You have to add tables names to every column or better use aliases for your tables:

SELECT SUM(`qty`)
     FROM MainStock ms, SparePart sp
     WHERE ms.sparePartID=sp.sparePartID;
     Group by sp.sparePartID

answered Aug 24, 2016 at 12:54

Jens

69.6k15 gold badges107 silver badges133 bronze badges

jophab · Accepted Answer · 2016-08-24 13:35:53Z

0

You are getting error because, both table contains the column sparePartId. So lets give the table name also to avoid ambiguity .

SELECT Mainstock.sparePartId, SUM(`qty`) 
FROM MainStock, SparePart
WHERE Mainstock.sparePartID=SparePart.sparePartID;

answered Aug 24, 2016 at 12:55

jophab

5,54714 gold badges45 silver badges61 bronze badges

This only solves his error, it doesn't answer his question.