CODEWITHSUNDEEP
mysqlsqlanti-join
santa
santa

Reputation: 12512

Selecting users from one table when not in the other

I have a query that selects a registration date of a user from one table. 

SELECT DATE(registered)
FROM users
WHERE user_id= ".$currentUser."
ORDER BY registered ASC
LIMIT 1

I need to modify the original query by joining another table, to make sure that the user is not present in that second table ("access") where a member ID is below certain value and the ACCESS date is below certain date.

LEFT JOIN access ON (users.user_id = access.user_id)
WHERE access.user_id < 500
AND access.date ... after March 1, 2012

Not sure how to get those two together.

Upvotes: 0

Views: 120

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169334

You can use a correlated subquery, e.g.:

  SELECT DATE(u.registered)
    FROM users u
   WHERE u.user_id = ".$currentUser."

     AND NOT EXISTS (
             SELECT 1 
               FROM access a 
              WHERE u.user_id = a.user_id
                AND a.user_id < 500
                AND a.date > DATE '2012-03-01')

ORDER BY registered ASC 
LIMIT 1;

Some notes:
You still get a row back even if there is no matching row(s) in the access table.
You probably know this...ASC is the default sort order so you don't need to type it out.

Upvotes: 2

Guffa
Guffa

Reputation: 700362

You need to put the entire condition in the join, otherwise you will effectively turn it into an inner join:

...
LEFT JOIN access ON (
  users.user_id = access.user_id
  AND access.user_id < 500
  AND access.date >= '20120301'
)
WHERE access.user_id is null
...

Upvotes: 2

Related Questions