CODEWITHSUNDEEP
left-join
Mario Cordeiro
Mario Cordeiro

Reputation: 119

left join 2 tables not working

I have 2 tables:

Table1: 'op_ats'

 | ID1    | numero |id_cofre | id_chave | estadoAT
 |  1     |  111   |   1     |   3      |   1
 |  2     |  222   |   3     |   3      |   2
 |  3     |  333   |   1     |   4      |   2
 |  4     |  444   |   1     |   2      |   3

Table_2: 'op_ats_cofres_chaves'

 | ID2 | num_chave |
 |  1  |  A        |
 |  2  |  B        |
 |  3  |  C        |
 |  4  |  D        |
 |  5  |  E        |

I have this SQL:

SELECT chaves.*, ats.numero numAT, ats.estadoAT
FROM op_ats_cofres_chaves chaves
LEFT JOIN op_ats ats ON ats.id_chave_cofre = chaves.id AND ats.id_cofre = 1

With this I get the following result:

| ID2 | num_chave | numAT | estadoAT |
|  1  |  A        | 444   |   3      |
|  2  |  B        | NULL  |   NULL   |
|  3  |  C        | 111   |   1      |
|  4  |  D        | 333   |   2      |
|  5  |  E        | NULL  |   NULL   |

Now the problem is that I want to filter the rows that are in Table1 but only that have the column 'estadoAT' with values 1 and 2. I've tried to add the line

WHERE op_ats.estadoAT = 1 OR op_ats.estadoAT = 2

But this makes the following result:

| ID2 | num_chave | numAT | estadoAT |
|  1  |  A        | 444   |   3      |
|  3  |  C        | 111   |   1      |
|  4  |  D        | 333   |   2      |

Resuming... My intention is to get ALL rows in the Table2 and join the Table1 rows that have the 'id_cofre = 1' and '(estadoAT = 1 OR estadoAT = 2)'. Any help is appreciated.

Upvotes: 1

Views: 27

Answers (1)

Krzysztof Atłasik
Krzysztof Atłasik

Reputation: 22595

You have to move condition to JOIN clause instead of WHERE.

SELECT chaves.*, ats.numero numAT, ats.estadoAT
FROM op_ats_cofres_chaves chaves
LEFT JOIN op_ats ats ON ats.id_chave_cofre = chaves.id AND ats.id_cofre = 1
AND op_ats.estadoAT = 1 OR op_ats.estadoAT = 2;

Upvotes: 1

Related Questions