SQL Query Problem

[arkman]

I'm having trouble getting some data extracted from a db in the manner I'm looking for. The db looks like this:

tbl.product
- prod_id
- dist_id
- name
- date

tbl.sales1
- prod_id
- cat_id
- sold

tbl.sales2
- prod_id
- cat_id
- sold

tbl.category
- cat_id
- cat_name

I want to extract the data so that it is the format:

prod_id, name, date, sold(sales1), sold(sales2), cat_name

The same prod_id can be in both sales tables, or not.

So far I've got:

select p.prod_id, p.name, p.date,
(select sold from sales1 where sales1.prod_id = p.prod_id) as sales1_volsold,
(select sold from sales2 where sales2.prod_id = p.prod_id) as sales2_volsold
from product as p
where p.dist_id=111

This gets me the sales fine, but I'm not sure how to include the cat_name for each product with the sales.

Any help is appreciated.

[rulian]

use JOINS rather then subqueries

[arkman]

I figured that might be the case, but I'm not sure where to go.

[arkman]

Yeah, I'm stuck. Anyone?

[paulnaj]

Something like ...

SELECT 	product.prod_id
, 	product.name
, 	product.date,
,	sales1.sold as sales1_volsold
,	sales2.sold as sales2_volsold
,	category1.cat_name as category1_name
,	category2.cat_name as category2_name
FROM product
LEFT JOIN sales1 ON (sales1.prod_id = product.prod_id)
LEFT JOIN category AS category1 ON (category1.cat_id = sales1.cat_id)
LEFT JOIN sales2 ON (sales2.prod_id = product.prod_id)
LEFT JOIN category AS category2 ON (category2.cat_id = sales2.cat_id)
WHERE product.dist_id = 111 

P 🙂

[nemonoman]

Watch out for the extra comma on line 3

You'll need to determine LEFT vs INNER joins. INNER joins would show no rows if there were no sales.

If you want TOTAL sales

SELECT 	product.prod_id
, 	product.name
, 	product.date
,	SUM(sales1.sold) as sales1_volsold
,	SUM(sales2.sold) as sales2_volsold
,	MAX(category1.cat_name) as category1_name
,	MAX(category2.cat_name) as category2_name
FROM product
LEFT JOIN sales1 ON (sales1.prod_id = product.prod_id)
LEFT JOIN category AS category1 ON (category1.cat_id = sales1.cat_id)
LEFT JOIN sales2 ON (sales2.prod_id = product.prod_id)
LEFT JOIN category AS category2 ON (category2.cat_id = sales2.cat_id)
WHERE product.dist_id = 111 
GROUP BY 1