user3007270 user3007270 - 1 year ago 74
MySQL Question

How to Left Join two MySQL tables on two columns?

I have two tables that I'm trying to join.

Table1: T1

--- ----- ------
A1 01 2
A1 02 2
A2 02 3

Table2: T2

--- ----- ------
A1 01 1
A2 02 0
A3 02 2

The output:

--- ----- --- ---
A1 01 2 1
A1 02 2 0
A2 02 3 0

Each id has multiple dates and there are different hits/runs per date.

I have tried:

SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON ( AND (;

This gives me a HUGE table. With way more rows than T1.

There are more rows in T2 than T1, but I only care about dates in T1 hence the LEFT JOIN.

Answer Source

It's hard to understand what you're trying to do without seeing a sample of data and what you expect to be output, but I'm going to guess that you don't have a correct understanding of what LEFT JOIN does.

T1 LEFT JOIN T2 does not limit the number of rows to one per row of T1.

If there are multiple rows in T2 that match a given row in T1, the result of the join will include all the matching rows of T2.

What T1 LEFT JOIN T2 does is output a given row from T1 even if there are no rows in T2 that match.

I'm guessing you probably want something like: output each row from T1, joined to the latest matching row from T2. If so, there are many solutions already posted on Stack Overflow. Look for questions tagged .

Re your comment:

Thanks, I tested your query with the test data you supplied. It does not give the result you show. The result is:

SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON ( AND (
| id   | date | hits | id   | date | runs |
| A1   | 01   |    2 | A1   | 01   |    1 |
| A1   | 02   |    2 | NULL | NULL | NULL |
| A2   | 02   |    3 | A2   | 02   |    0 |


I think you are running a query different from what you have posted above. Maybe you mixed up AND versus OR in your join condition?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download