CODEWITHSUNDEEP
mysqlsqlleft-join
Joey Lant
Joey Lant

Reputation: 59

Two Step Left Join In MySQL

I'm trying to left join two tables. Table 1 (tb1) is like:

DATE_         ID           NAME
2007-09       1            Apple
2007-10       2            Pear
2007-11       3            Banana
2007-12       null         Cherry
2008-01       11           Kiwi
  ...         ...           ...

And Table 2 (tb2) is like:

 DATE_         ID           NAME      Sales
2007-09       1            Apple       200
2007-10       2            Pear        100
2007-11       3            Banana      300
2007-12       4            Cherry      500
  ...         ...           ...        ...

I want to first left join two tables using ID first. For the records in Table 2 where ID is null I want to use name as the key.

I know I can first left join Table 1 on tb1.ID = tb2.ID and tb1.DATE_ = tb2.DATE, using the method create table my_table select as creating a new table, and then do the left join again for the new table. The problem is I have thousands of records in table 1 and 2. Even when I tried to create a new table for the first join using the ID, SQL Workbench gives me an error code saying connection lost.

create view my_view would solve the connection problem. But I wonder how can I do the two step join with a view? Thanks in advance.

Upvotes: 0

Views: 499

Answers (1)

peterm
peterm

Reputation: 92805

Are you looking for something like this?

SELECT *
  FROM table1 t1 LEFT JOIN table2 t2
    ON t1.date = t2.date
   AND (t1.id = t2.id OR (t1.id IS NULL AND t1.name = t2.name));

Sample output:

+------------+------+--------+------------+------+--------+-------+
| DATE       | ID   | NAME   | DATE       | ID   | NAME   | Sales |
+------------+------+--------+------------+------+--------+-------+
| 2007-09-01 | 1    | Apple  | 2007-09-01 |    1 | Apple  |   200 |
| 2007-10-01 | 2    | Pear   | 2007-10-01 |    2 | Pear   |   100 |
| 2007-11-01 | 3    | Banana | 2007-11-01 |    3 | Banana |   300 |
| 2007-12-01 | NULL | Cherry | 2007-12-01 |    4 | Cherry |   500 |
| 2008-01-01 | 11   | Kiwi   | NULL       | NULL | NULL   |  NULL |
+------------+------+--------+------------+------+--------+-------+

Here is a dbfiddle demo

Upvotes: 1

Related Questions