How to join three select queries which has one common column

Question

I have three select queries as below which gives a respective output

select DATE_FORMAT(table1.value_date,'%b')as Month,
       DATE_FORMAT(table1.value_date,'%Y') as Year,
       table1.open as Open 
from index_main as table1 
    join ( select min(`value_date`) `value_date` 
           from index_main 
           group by month(`value_date`), year( `value_date`) 
    ) as table2 on table1.`value_date` = table2.`value_date`

Output columns - Month,year,open

select DATE_FORMAT(table1.value_date,'%b')as Month,
    DATE_FORMAT(table1.value_date,'%Y') as Year,
    table1.close as Open 
from index_main as table1 
    join ( select max(`value_date`) `value_date` 
           from index_main group by month(`value_date`), year( `value_date`) 
    ) as table2 on(table1.`value_date` = table2.`value_date`)

Output columns - Month,year,close

select DATE_FORMAT(table1.value_date,'%b')as Month,
    DATE_FORMAT(table1.value_date,'%Y') as Year,
    max(table1.high) as High 
FROM `index_main` as table1 
GROUP BY table1.month,table1.year 
ORDER BY year(table1.value_date) desc, month(table1.value_date) desc

Output columns - Month,year,high,low

I want to join these three select queries based on the common columns i.e month & year.

My final result should have the following columns - month,year,open,close,high,low.

Possible duplicate: http://stackoverflow.com/questions/21889371/derived-table-in-sql-server — Andrew, Sep 15 '14 at 18:04

score 0 · Answer 1 · answered Sep 15 '14 at 18:08

Try this.

First create 3 views, one with each query (vw1, vw2 and vw3). Then use a query like this:

SELECT vw1.Month, vw1.Year, Open, Close High FROM vw1 LEFT join vw2 on vw1.Year=vw2.Year and vw1.Month=vw2.Month LEFT JOIN vw3 on vw1.Year=vw3.Year and vw1.Month=vw3.Month

Hope this helps you.

How to join three select queries which has one common column

1 Answers1