MySQL: Noob join question

Question

MySQL noob with a table-joining question:

I have two tables, "splits" and "dividends" that describe events in the stock market.

The splits table has "day", "ratio", and "ticker". The dividends table has "day", "amount", and "ticker".

I would like to get a resulting joined table that has both tables' information, AND sorted by date and ticker. So something like this: (sorry about the formatting)

splits.day splits.ratio splits.ticker dividends.day dividends.amount dividends.ticker

1990-01-03      2            QQQQ          null           null            null

null          null            null        1995-05-05      15.55          SPY

2000-09-15     3             DIA           null            null           null

null            null           null        2005-03-15     3             DIA

I looked up full outer joins on wikipedia (using unions on mysql) but I couldn't figure out how to get it to be sorted by the day... Any help would be greatly appreciated!

EDIT: here's an example of what splits and dividends contain in the above example

splits.day splits.ratio splits.ticker

1990-01-03      2            QQQQ

2000-09-15     3             DIA



dividends.day dividends.amount dividends.ticker

1995-05-05      15.55          SPY

2005-03-15     3.55             QQQQ

Answer 1

to do Full Outer Join :

select splits.day splits.ratio splits.ticker dividends.day dividends.amount dividends.ticker from splits , dividends where splits.ticker = dividends.ticker order by splits.day

Answer 2

I fail to understand why do you need the nulls you use in your example, does this mean not every row in one table has every "ticker" on the other? Is even "ticker" the relation between the two tables?

If all the assumptions above are correct, then something like this should be what you want:

select splits.*, dividends.* from splits left join dividends 
on (splits.ticker = dividends.ticker) 
order by splits.day, dividends.day, splits.ticker

EDIT: Based on the comments, you might be looking for a union

select a.ticker,a.ratio_or_amount,a.day,a.what from (
select ticker, day, ratio as ratio_or_amount, 'splits' as what from splits
union 
select ticker, day, amount as ratio_or_amount, 'dividends' as what from dividends
) a order by a.day

This is ugly, but I'm really failing at what you are after. It looks like you miss a refresher on relational theory.

Answer 3

You might want to have a look at GREATEST() ( http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest ) in order to create a comparable date row. Do not, however, that the NULL values might be a problem in MySQL >= 5.0.13.

Answer 4

You shouldn't have to use unions. You should be able to do this (sorry, I don't have MySQL available to test):

select 
    ifnull(splits.day, dividends.day) as day, splits.ratio, 
    ifnull(splits.ticker, dividends.ticker) as ticker, dividends.amount
from splits 
    outer join dividends on 
        splits.day = dividends.day and splits.ticker = dividends.ticker
order by day, ticker

Answer 5

OK, based on your edits, it looks to me like you don't really want a join at all. You probably want to do this:

select *
from 
(
    select day, ticker, ratio, null as amount
    from splits

    union

    select day, ticker, null as ratio, amount
    from dividends
) as q
order by day, ticker