i'd like to create a view from 2 unrelated tables.
the follow works except it puts the all the data into 2 columns.
i'd like the schema to look more like the grid below.
is there a way to do this in mysql5
Code: Select all
SELECT ri.product_id AS Product , SUM(ri.amount) AS Returned
FROM returns_table r, return_items ri
WHERE ri.returns_id = r.id AND r.date
BETWEEN '2007-07-26' AND '2007-09-06' AND r.client_id = '5082'
GROUP BY ri.product_id
UNION
SELECT ii.product_id AS Product , SUM(ii.amount) AS Delivered
FROM invoices i, invoice_items ii
WHERE ii.invoice_id=i.id AND i.date
BETWEEN '2007-07-26' AND '2007-09-06' AND i.client_id = '5082'
GROUP BY ii.product_id| Product | Delivered | Returned | Sold |
---------------------------------------------------
| Name | 20 | 10 | 10 |
| Name | 15 | 0 | 15 |
| Name | 5 | 15 | -10 |
---------------------------------------------------
tia, Will.