trouble with query

[ryan010101]

I have two tables. One lists SKUs that are on "programs". The other table has sales info for all SKUs we sell, not totally up (each record is a line item on an order, so the SKU repeats many times).

I have a page which displays all the info for the particular program (stored in a table different from the above two) and then it displays what SKUs are on that program. In the list of SKUs that are on the program I would like to display the sales total for that particular SKU.

This is the current SQL statement

SELECT items_on_programs.*, FROM items_on_programs WHERE items_on_programs.program_id = " & request("id")

What needs to be done to not just grab the SKUs on a particular program but also grab the sales total for those SKUs from the other table?


thanks
Ryan

 

[PHV]

Can you please the schema of the 3 involved tables with relationships ?

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

 

[ryan010101]

The tables are:

items_on_programs
-id
-program_id
-sku
-controlled_funds
-approved
-buyin_expected
-buyin_actual

excsales
-invoice_num
-customer_num
-sku
-qty_shipped
-qty_ordered

Only two tables are involved (the program info is displayed but a different query is used). excsales is a line listing of orders. SKUs will repeat many times. So on the page it will display all items that are with a program_id. It needs to display all the data in items_on_programs plus for the skus in items_on_programs it needs to total up the qty_ordered for that SKU in excsales.

Any help would be appriciated.
thanks
Ryan

 

[PHV]

Something like this ?
SELECT i.id, i.program_id, i.sku, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual
, Sum(Nz(e.qty_ordered)) As Qty
FROM items_on_programs i LEFT JOIN excsales e ON i.sku = e.sku
WHERE i.program_id = " & request("id")
GROUP BY i.id, i.program_id, i.sku, i.controlled_funds, i.approved, i.buyin_expected, i.buyin_actual

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

 

[ryan010101]

Except for the Nz() part, it works! Thanks so much!

-Ryan

 

[lespaul]

What do you mean:

Except for the Nz() part, it works

Leslie

 

[ryan010101]

Ok, I forgot something. There is another table involved. I need to also display the title and vendor of the SKU in question. All of the SKU info is in another table called "catalog". SO I need to also select productname from catalog where productname.sku = items_on_programs.sku.

thanks
Ryan

 

[ryan010101]

When i tried the query with Nz() it didn't work (i got an error), when I took it out it did work.

 

[ryan010101]

Can anybody help me with how to get another table into the above query?

thanks