Clause where in mysql

Question

I have two tables.

Table db1 AND db2

DB1
+-----------+------------+
|    id     |    nameDb  |
+-----------+------------+
|    1      |    name1   |
+-----------+------------+
|    2      |    name2   |
+-----------+------------+
|    3      |    name3   |
+-----------+------------+

DB2

+------------+------------+-------------+----------------------+
|    id      |   name     |   id_db1    |          date        |
+------------+------------+-------------+----------------------+
|    1       |   test1    |      1      |  2013-05-10 10:00:00 |
+------------+------------+-------------+----------------------+
|    2       |   test2    |      1      |  2013-05-10 11:00:00 |
+------------+------------+-------------+----------------------+
|    3       |   test3    |      1      |  2013-05-10 11:10:00 |
+------------+------------+-------------+----------------------+
|    4       |   test4    |      1      |  2013-05-10 11:40:00 |
+------------+------------+-------------+----------------------+

my query is this:

SELECT a.nameDb, b.name FROM db1 a 
LEFT JOIN db2 b ON b.id_db1 = a.id 
WHERE DATE_FORMAT(b.name, '%Y-%m-%d') = '2013-05-10'

I need to return everything related to the day, which was not related must also appear...

Something Like

+------------+------------+
|    nameDb  |   name     |
+------------+------------+
|    name1   |   test1    |
+------------+------------+
|    name1   |   test2    |
+------------+------------+
|    name1   |   test3    |
+------------+------------+
|    name1   |   test4    |
+------------+------------+
|    name2   |   NULL     |
+------------+------------+
|    name3   |   NULL     |
+------------+------------+

any idea ?

score 2 · Accepted Answer · edited May 02 '13 at 18:22

2

try this

SELECT a.nameDb, b.name FROM db1 a 
LEFT JOIN db2 b ON b.id_db1 = a.id 
and DATE_FORMAT(b.date, '%Y-%m-%d') = '2013-05-10'

If you use columns from left outer joined tables in where clause it behaves like inner join

edited May 02 '13 at 18:22

Joachim Isaksson

176,943
25
281
294

answered May 02 '13 at 18:19

rs.

26,707
12
68
90

but consultation is being used – Igor Ciqueira May 02 '13 at 18:21
@IgorCiqueira, i didn't understand, what are you trying to say when you say "consultation is being used" – rs. May 02 '13 at 18:23
I'm sorry ... I am using is query but I can not view the data – Igor Ciqueira May 02 '13 at 18:27
@IgorCiqueira, it returns data check this [fiddle link](http://sqlfiddle.com/#!2/c36e8/3), notice that your original query is using b.name and not b.date in DATE_FORMAT function – rs. May 02 '13 at 18:29
I actually checked, but in my query the left join is behaving like inner – Igor Ciqueira May 02 '13 at 18:31
@IgorCiqueira, where did appointment come from? can you create sql fiddle link with data and your actual query? – rs. May 02 '13 at 18:33

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

You want an outer join. There's a good description of the different joins and their outputs here: What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited May 23 '17 at 12:04

Community

1
1

answered May 02 '13 at 18:19

Stephen Fischer

2,445
2
23
38

Matt Busche · Answer 3 · 2013-05-02T18:28:10.353

0

You should just need a RIGHT JOIN or swap the tables around and you can use a LEFT JOIN

SELECT a.nameDb, b.name 
FROM db1 a 
  RIGHT JOIN db2 b ON b.id_db1 = a.id AND DATE_FORMAT(b.name, '%Y-%m-%d') = '2013-05-10'

edited May 02 '13 at 18:28

answered May 02 '13 at 18:20

Matt Busche

14,216
5
36
61

score 0 · Answer 4 · answered May 02 '13 at 18:20

0

You should change the WHERE to an AND, to make the date check as part of your join condition:

SELECT a.nameDb, b.name FROM db1 a 
LEFT JOIN db2 b ON b.id_db1 = a.id 
AND DATE_FORMAT(b.name, '%Y-%m-%d') = '2013-05-10'

answered May 02 '13 at 18:20

Ken Richards

2,937
2
20
22

Clause where in mysql

4 Answers4