SQL Query to join Two tables

Question

I have two table as follows :-

 table1                                table2
 date      time  amount                date      time amount
 20120101  1000   101                  20120104  1000   10
 20120101  1100   100                  20120104  1100   11
 20120104  1000   101                  20120105  1000   11
 20120104  1100   105                  20120105  1100   8

I want to join these two tables to get the output as follows :

 date      time  table1-amt   table2-amt
 20120101  1000   101          NULL
 20120101  1100   100          NULL
 20120104  1000   101           10
 20120104  1100   105           11
 20120105  1000   NULL          11
 20120105  1100   NULL          8

What is the sql query to get this output? I am using mysql database.

I tried following query:

  select table1.date,table1.time,table1.close , table2.close 
  from table1,
        table2 
  where table1.date=table2.date 
  and table1.time=table2.time;

it gave me output as

    date       time  amount       amount
    20120104   1000   101           10
    20120104   1100   105           11

People are directing me towards left outer join , full outer join I tried following two queries which did nt solve my purpose .

   select * from table1 left join table2 on table1.date=table2.date ;

  select * from table1 left join table2 on table1.date=table2.date union select * from table1 right join table2 on table1.date=table2.date;

possible duplicate of [mysql left outer join](http://stackoverflow.com/questions/3058834/mysql-left-outer-join) — APC, Apr 30 '13 at 11:09
When you say you don't have a unique id, do you mean that your tables can have multipl erecords for the same combination of (DATE, TIME)? Or just that you haven't bothered top define a primary key? — APC, Apr 30 '13 at 12:07

score 2 · Accepted Answer · answered Apr 30 '13 at 11:19

2

An approach that only involves reading from each of the tables once:

SELECT `date`, `time`, sum(`amt1`) as `table1-amt`, sum(`amt2`) as `table2-amt` 
FROM
(SELECT `date`, `time`, amount as amt1, null as amt2
 FROM Table1
 UNION ALL
 SELECT  `date`, `time`, null as am1, amount as amt2
 FROM Table2) v
GROUP BY `date`, `time`

(As opposed to the examples linked in Yordi's answer, which each read from each table twice.)

answered Apr 30 '13 at 11:19

@Mark : people are directing me towards left outer join , full outer join but I noticed that in these methods they are using a where clause which is equating unique id in both tables. my tabless do not have such reation between them . – Virendra Bisht Apr 30 '13 at 11:38
@VirendraBisht: As I understand it, you want to see a resultset that has unique combinations of date and time, together with total amounts from each table for that date and time. If each of your tables has no more than one entry for any given date and time, then what you want would be an example of one table full outer joined to the other on date and time. On the other hand, if you could have multiple entries on either table for a given date and time, then you would need to summarise each table by date and time, and then full outer join the summarised datasets on date and time. – Apr 30 '13 at 11:49
(This solution achieves either objective using a single read of each table.) – Apr 30 '13 at 11:50
1

@Mark : thanks for explanation mark . by equating unique id I mean like a emplyee-id column in both table but date and time is primary key in each table. now is it possible to write a left outer join or full outer join query to get desired solution. your query solves my purpose but as i have to apply this query on more than 10 table at a time having 100k record in each table therefore i am looking for a simpler query. – Virendra Bisht Apr 30 '13 at 12:11
1

@VirendraBisht: Full outer joining where there is more than one key field is exactly the same as full outer joining on a single unique ID - the only difference is that you have to explicitly use more than one condition linking the two tables (one condition for each key field). MySQL does not directly support full outer joins, which is why every answer you have received on this subject is how to simulate full outer joins in MySQL. I think this answer is simplest - if you find a simpler one, do let us know. Also, if you have to full outer join 10 tables, your schema may be improperly normalised. – Apr 30 '13 at 12:29

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

This is what you want

Full Outer Join in MySQL

Not going to just give the answer, you'll find i there and learn some.

EDIT : Oh well someone beat me to it, and just handed it to you ^^.

edited May 23 '17 at 10:25

Community

1
1

answered Apr 30 '13 at 11:05

Yordi

195
1
8

there is no unique id between these table that is why I am not getting a solution from left outer join or full outer join. thanks – Virendra Bisht Apr 30 '13 at 11:32
I tried following two queries which did gave me desired output would you please help me to figure out my desired output. " select * from table1 left join table2 on table1.date=table2.date union se lect * from table1 right join table2 on table1.date=table2.date;" and other one is " select * from table1 left join table2 on table1.date=table2.date ;" thanks – Virendra Bisht Apr 30 '13 at 12:01

score 0 · Answer 3 · answered Apr 30 '13 at 12:07

You need a FULL (outer) JOIN. One way to achieve it in MySQL:

SELECT t1.date, t1.time, t1.close AS table1_amt, t2.close AS table2_amt 
FROM table1 AS t1
  LEFT JOIN table2 AS t2
    ON  t1.date = t2.date 
    AND t1.time = t2.time

UNION ALL

SELECT t2.date, t2.time, t1.close, t2.close 
FROM table1 AS t1
  RIGHT JOIN table2 AS t2
    ON  t1.date = t2.date 
    AND t1.time = t2.time
WHERE t1.date IS NULL ;

SQL Query to join Two tables

3 Answers3