CODEWITHSUNDEEP
mysql
Igor Ciqueira
Igor Ciqueira

Reputation: 7

Clause where in mysql

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 ?

Upvotes: 0

Views: 45

Answers (4)

Matt Busche
Matt Busche

Reputation: 14333

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'

Upvotes: 0

rs.
rs.

Reputation: 27467

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

Upvotes: 2

Ken Richards
Ken Richards

Reputation: 3021

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'

Upvotes: 0

Stephen Fischer
Stephen Fischer

Reputation: 2546

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"?

Upvotes: 0

Related Questions