CODEWITHSUNDEEP
mysql
Kirby
Kirby

Reputation: 316

Mysql create table composite/join of two other tables

I'm sure this is simple.. I'm just not seeing the correct syntax..

I have two tables:

select * from buyTable;
+---------+------+------------+
| Quarter | Year | buyerAgent |
+---------+------+------------+
|       4 | 2007 |          1 | 
|       1 | 2008 |          2 | 
|       2 | 2008 |          1 |

and

mysql> select * from sellTable;
+---------+------+-------------+
| Quarter | Year | sellerAgent |
+---------+------+-------------+
|       2 | 2008 |           1 | 
|       3 | 2008 |           1 |

This is what I'm wanting:

mysql> select ?????????????
+---------+------+-------------+-------------+
| Quarter | Year | sellerAgent | buyerAgent |
+---------+------+-------------+-------------+
|       4 | 2007 |           0 |          1 | 
|       1 | 2008 |           0 |          2 | 
|       2 | 2008 |           1 |          1 |
|       3 | 2008 |           1 |          0 |

The zeros can be nulls.. Don't care about. It seems like this shouldn't be too hard, but I haven't managed to coerce mysql into doing what I want yet.

Update:

Using the two suggestions.. where a select was given for a left join, and a suggestion to use a full outer join.. I came up with the following. In my case, buyTable and sellTable were mysql temporary tables. There's a condition on using temporary tables in mysql that you can't reopen a table for the same query. So, I had to make a second temporary table for both buy and sell that had the same contents. I called them buyTable2 and sellTable2. If you are using actual mysql tables you won't need to do that, and can revise the following query appropriately.

Also, this query adds a third column that sums up the buy and sell columns. In addition, it replaces NULL entries, where there wasn't any buy (or sell) for a given quarter with Zero.

(select buyTable.Quarter, buyTable.Year, ifnull(sellerAgent,0), ifnull(buyerAgent,0), 
        ifnull(sellerAgent,0)+ifnull(buyerAgent,0) 
  FROM buyTable 
  LEFT JOIN  sellTable ON 
    (buyTable.quarter = sellTable.quarter AND buyTable.year = sellTable.year)) 
union 
(select sellTable2.Quarter, sellTable2.Year, ifnull(sellerAgent,0), ifnull(buyerAgent,0),
        ifnull(sellerAgent,0)+ifnull(buyerAgent,0) 
  FROM buyTable2 
  RIGHT JOIN  sellTable2 ON 
    (buyTable2.quarter = sellTable2.quarter AND buyTable2.year = sellTable2.year));

Upvotes: 0

Views: 69

Answers (2)

saikumarm
saikumarm

Reputation: 1575

select quarter, year, sellerAgent, buyerAgent 
from buyTable full outer join sellTable 
using(year,quarter);

Upvotes: 0

Tushar Bhaware
Tushar Bhaware

Reputation: 2525

I think this should do the trick,

select buytable.quarter, buytable.year, sellerAgent, buyerAgent FROM buyTable LEFT JOIN  sellTable ON (buyTable.quarter = sellTable.quarter AND buyTable.year = sellTable.year)
UNION
select sellTable.quarter, sellTable.year, sellerAgent, buyerAgent FROM buyTable RIGHT JOIN  sellTable ON (buyTable.quarter = sellTable.quarter AND buyTable.year = sellTable.year)

Use UNION if you don't want duplicate entries, use UNION ALL if you want duplicate entries.

Upvotes: 2

Related Questions