Lunatic Fnatic Lunatic Fnatic - 1 month ago 6
MySQL Question

MySQL "AND" statement issue

I have 2 table (sample below)

aTable
-------------------------------------------------
name | datein | dateout
test | 2016-10-10 00:00:00 | null --->> This one
test2 | 2016-10-12 00:00:00 | null
-------------------------------------------------

bTable
------------------------------------------------------------
name | datein | dateout
test | 2016-05-05 00:00:00 | 2016-06-06 00:00:00
test | 2016-10-10 00:00:00 | null --->> This one
test2 | 2016-10-12 00:00:00 | 2016-10-13 00:00:00
------------------------------------------------------------


I want to extract the data where both
name
,
datein
are equal and
dateout
is null

I am using the query below, but I get empty response.

SELECT name,datein
FROM aTable
WHERE (name = 'test' AND dateout IS NULL) AND
(SELECT name FROM bTable WHERE name = 'test' AND dateout IS NULL)


Is there any point I am missing?

Answer

You need to add EXISTS to your query:

SELECT t1.name, t1.datein 
FROM aTable AS t1
WHERE (t1.name = 'test' AND t1.dateout IS NULL) AND 
      EXISTS(SELECT name 
             FROM bTable AS t2
             WHERE t2.name = 'test' AND 
                   t2.datein = t1.datein AND
                   t2.dateout IS NULL)

or, without supplying a specific value for name field:

SELECT t1.name, t1.datein 
FROM aTable AS t1
WHERE t1.dateout IS NULL AND 
      EXISTS(SELECT name 
             FROM bTable AS t2
             WHERE t2.name = t1.name AND 
                   t2.datein = t1.datein AND
                   t2.dateout IS NULL)
Comments