mysql view using unrelated tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets1
Forum Newbie
Posts: 1
Joined: Wed Sep 05, 2007 11:29 pm

mysql view using unrelated tables

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

They sure look related to me. Can't you do a join on product_id?
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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.
Post Reply