help, need to add data from another table to this query

[ryan010101]

Three tables:
items_on_programs, execsales, catalog

Below is the current query I have which uses items_on_programs and execsales. The results displays a list of SKUs and shows how manu were ordered (SUM(e.qty_orderd). I need to not just display the SKU, but also the product name and UPC for the SKU in question, this is stored in the table "catalog". I'm not sure how to go about adding that table into the mix. Any help would be appreciated.

SELECT i.id, i.program_id, i.sku, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost, Sum(e.qty_ordered) As Qty FROM items_on_programs i LEFT JOIN excsales e ON i.sku = e.sku
WHERE i.program_id = 22
GROUP BY i.id, i.program_id, i.sku, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost

thanks
Ryan

 

[PHV]

Any chance you could post your database schema showing how product name and UPC are related to SKU ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryan010101]

catalog" looks like this:
sku
product (this is the product name)
upc

There are other fields but these are the only ones I need.
For more bg on what I'm doing, this is being used on a web page. There are "programs" and SKUs are assigned to programs. These SKUs that are on programs are stored in "items_on_programs", sales info for these SKUS are stored in "excsales". Someone will click on a list of programs to display details about the program, including what SKUs are on the program. In this list of SKUs, I'm displaying how many of each sku has been sold. I want to be able to show now only the SKU but it's productname and UPC, and these are stored in "catalog". Hope that explains everything.

 

[PHV]

SELECT i.id, i.program_id, i.sku, c.product, c.upc, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost, Sum(e.qty_ordered) As Qty
FROM (items_on_programs i LEFT JOIN excsales e ON i.sku = e.sku)
INNER JOIN catalog c ON I.sku=c.sku
WHERE i.program_id = 22
GROUP BY i.id, i.program_id, i.sku, c.product, c.upc, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryan010101]

Thanks for the help!

-Ryan

 

[ryan010101]

I need to modify this query again. Currently I'm summing the "qty_ordered" from the table "excsales". Another field in the table "excsales" is customer_num. I am going to provide a customer number and I want to only sum the qty_ordered for that particular customer. Can that criteria also be added to this query?

I tried just adding to the WHERE clause with e.customer_num = cn (cn being a variable I'm populating prior to this query) but that doesn't give the desired results.

thanks
Ryan

 

[DreewToo]

This should do it...

SELECT i.id, i.program_id, i.sku, c.product, c.upc, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost, e.customer_num, Sum(e.qty_ordered) As Qty
FROM (items_on_programs i LEFT JOIN excsales e ON i.sku = e.sku)
INNER JOIN catalog c ON I.sku=c.sku
WHERE i.program_id = 22
GROUP BY i.id, i.program_id, i.sku, c.product, c.upc, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, e.customer_num, i.item_cost

 

[ryan010101]

Thanks for the response.

Isn't that just going to make it so customer_num is included in the results? I need to be able to specify a customer_num and have only their sales summed in Sum(e.qty_ordered).

thanks

 

[lespaul]

SELECT i.id, i.program_id, i.sku, c.product, c.upc, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost, Sum(e.qty_ordered) As Qty
FROM (items_on_programs i LEFT JOIN excsales e ON i.sku = e.sku)
INNER JOIN catalog c ON I.sku=c.sku 
WHERE i.program_id = 22 [b]AND Customer_num = [Enter Customer Number][/b]
GROUP BY i.id, i.program_id, i.sku, c.product, c.upc, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual, i.item_cost

Leslie

 

[ryan010101]

Thanks but I didn't mention this before but I need it to display items that are on programs even if the customer hasn't made any purchases yet. Add that to the where clause and it only returns items on the program that the specified account has purchased. I need it to display all items on the programs whether or not there are in the excsales table or not.

thanks

 

[ryan010101]

Anybody else what to take a stab at this??

thanks
Ryan

 

[GingerR]

this looks tremendously familiar...

thread701-945001

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryan010101]

GingerR, I'm not sure what your point is. Should I not ask a question because I previously asked a similiar question months ago (and never received a response)?

 

[GingerR]

Not at all. But I see in your last post, the first thing PHV asks is for your table schema. Same thing here. And in your last post, you add more to your question/issue bit by bit as the post progresses....same as here. Just noticing a pattern, that's all. You might get better results if you include all of this information up front instead of dragging it out over many posts.........Just a suggestion.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244