coopster
msg:4534670 | 3:02 pm on Jan 9, 2013 (gmt 0) |
You'll need to join the two tables. But I do not see any common keys shared between the two tables to join them?
|
generic
msg:4534686 | 3:24 pm on Jan 9, 2013 (gmt 0) |
The prod_man field holds the id of the manufacturer (man_id) but that's as common as it gets. I just can't wrap my head around joins at all, no matter how many books or tutorials I look at.
|
coopster
msg:4534689 | 3:31 pm on Jan 9, 2013 (gmt 0) |
So if a manufacturer has 10 different products, you only want one line to show with the total quantity of all 10 different products summed up?
|
generic
msg:4534692 | 3:42 pm on Jan 9, 2013 (gmt 0) |
Yea, it's basically just so I can tally total amount of stock by manufacturer. So if there are two manufacturers, Acme and WidgetCo, I'd like it loop through and return the total sum of prod_qty where prod_man = man_id
Acme: 49 unique products (246 items in stock)
WidgetCo: 26 unique products (421 items in stock)
Or something like that. We're tracking t-shirt transfers so we don't want to enter each individual transfer (and amount of each transfer in stock) as inventory, we'd rather just keep track of how many transfers we have, in total, by manufacturer.
|
coopster
msg:4534693 | 3:45 pm on Jan 9, 2013 (gmt 0) |
Understood. It would looks something like this ...
SELECT
m.man_name AS Manufacturer,
SUM(prod_qty) AS Qty
FROM manufacturers AS m
INNER JOIN products AS p ON (m.man_id = p.prod_man)
GROUP BY m.man_name
ORDER BY m.man_name
;
You select from your primary table which is the manufacturers and join the products table on the primary key, manufacturer identifier. In order to SUM you need to use a GROUP BY.
|
generic
msg:4534695 | 4:00 pm on Jan 9, 2013 (gmt 0) |
That's amazing. I just can't seem to wrap my head around joins and my code is about four times longer and uglier than it needs to be because of that. That is exactly what I needed. Thank you so much coopster, I really appreciate your help!
|
skoff
msg:4534768 | 9:08 pm on Jan 9, 2013 (gmt 0) |
you should read about inner join and all that stuff it would really help your query to run faster and try to avoid using select inside a select method this is hard on the server! Coopster got it right
|
generic
msg:4534769 | 9:12 pm on Jan 9, 2013 (gmt 0) |
I completely agree. I know I needed to use joins but I just can't wrap my head around them no matter how many times I try. I'm tenacious though, one of these days the idea will stick. Cheers.
|
|
