CODEWITHSUNDEEP
mysqldatetime
Lunatic Fnatic
Lunatic Fnatic

Reputation: 681

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?

Upvotes: 0

Views: 48

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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)

Upvotes: 2

AssenKhan
AssenKhan

Reputation: 566

Try this 

  SELECT name,datein,dateout FROM(select * from atable union select * from btable)as tt 
where tt.name ='test' and tt.dateout is null;

Upvotes: 1

Related Questions