CODEWITHSUNDEEP
mysqlsql
S. Sandeep
S. Sandeep

Reputation: 257

Using a table to loop from a SQL Query

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?

Upvotes: 0

Views: 57

Answers (3)

jophab
jophab

Reputation: 5509

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;

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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

Upvotes: 3

Jens
Jens

Reputation: 69440

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

Upvotes: 1

Related Questions