Page 1 of 1

mysql view using unrelated tables

Posted: Thu Sep 06, 2007 12:47 am
by facets1
hi,

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.

Posted: Thu Sep 06, 2007 7:48 am
by Begby
They sure look related to me. Can't you do a join on product_id?

Posted: Thu Sep 06, 2007 10:07 am
by ReverendDexter
If those two tables *are* unrelated, you have a serious problem with your database schema. If there is no relation, there is *no way* for you to create the view you are wanting.