Selecting users from one table when not in the other

Question

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.

score 2 · Answer 1 · answered Mar 04 '12 at 04:59

2

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
...

answered Mar 04 '12 at 04:59

Guffa

687,336
108
737
1,005

"from one table when not in the other" = anti-join. – onedaywhen Mar 04 '12 at 07:53
@onedaywhen: Yes, that's what you do when you use a left join and check for null values. – Guffa Mar 04 '12 at 10:45
@onedaywhen: If you are lucky the query planner will turn `not exists()` into a left join, because it's much more efficient. – Guffa Mar 04 '12 at 11:49
"Yes, that's what you do when you use a left join and check for null values" -- I'd say it was the other way around: the concept is existential quantification, the relational operator is semi-difference (a.k.a. anti-join) and the implementation in SQL has, as is usual, about seven different ways. Personally, I avoid nulls like the plague and outer join is expressly designed to product nulls. – onedaywhen Mar 05 '12 at 08:54
"If you are lucky the query planner will turn [your favoured choice of about seven candidate queries] into [the optimizer's favoured choice of about seven candidate queries]" -- I expect nothing less of a DBMS. – onedaywhen Mar 05 '12 at 08:56
@onedaywhen: No, it's not the other way around, it's exactly like I said. You can use a left join to check for non-existing relations. It's as simple as that. Regarding your choise of solution, you are using an irrational reason to avoid a specific solution, but on the other hand you expect the database to convert your solution of choise to that specific solution that you try to avoid. – Guffa Mar 05 '12 at 12:24
"You can use a left join to check for non-existing relations" -- did you mean 'tuples' rather than 'relations'? "an irrational reason" -- my reason for avoiding nulls is that they lead to mistakes (recall you made one yourself last week, for which I blame nulls and not you!) "you expect the database to" -- did you mean 'DBMS' rather than 'database'? "you expect the [DBMS] to convert your solution of choise to that specific solution that you try to avoid" -- no, I expect it to pick the best plan. – onedaywhen Mar 05 '12 at 14:37
@onedaywhen: No, I didn't mean tuples, I meant relations. Perhaps you thought that I meant relation between tables, but I mean relations between records. A relation between tables can't be non-existing, it's always there even if there are not records that fulfill it. Using null doesn't in itself lead to mistakes, so your reason for avoid null is not rational. Perhaps I could use "DBMS" sometimes when I use "database", but I don't make that distinction unless nessecary, because it serves no purpose. – Guffa Mar 05 '12 at 14:57

score 2 · Accepted Answer · answered Mar 04 '12 at 05:00

2

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.

answered Mar 04 '12 at 05:00

mechanical_meat

163,903
24
228
223

Great! I just thought of something. I do need the date to be a.date > DATE '2012-03-01' but it can not be today's date either... Can I just add AND a.date != NOW() ? – santa Mar 04 '12 at 05:14
1

Yeah, add one more line to the subquery: `AND a.date <> CURDATE()` – mechanical_meat Mar 04 '12 at 05:17

Selecting users from one table when not in the other

2 Answers2