I have two tables.
t1 contains user records t1.id t1.name
t1 contains user records
t1.id t1.name
and
t2 contains user posts t2.id t2.userID t2.post
t2 contains user posts
t2.id t2.userID t2.post
where t2.userID = t1.id
I need to select only users that DO NOT HAVE ANY POSTS in t2. It's gotta be something obvious but I'm blocked.
select * from t1 where not exists (select 1 from t2 where t2.userID = t1.id)
I can get this to work. I know it is correct syntax, but I just can't seem to get it to work. If I replace it with something like WHERE 1=1 it works fine. I'm not sure why it does not like NOT EXISTS...
Just figured it out. My mySQL client API version is 3.23.58. Found this in the manual:
EXISTS is supported from 4.1 on. Earlier versions of MySQL can try rewriting the query using a LEFT JOIN.
Have no idea how to use LEFT JOIN in this BACKWARD case...
The manual isn't talking about the client API - it's talking about the server version (your client API is actually newer than mine, 3.23.49... but I'm running MySQL server v. 5.0.27).
I checked and I actually have Database and Directory Services: MySQL 3.23
Doing a SELECT VERSION() query will show you the version, e.g. 5.0.27-community-nt is what's on my computer right now.
Didn't think anyone was still on 3.23 :queasy:
Try it this way (looks silly, but it's the old trick for NOT EXISTS when you can't do a subselect):
select * from t1 left join t2 on t2.userID = t1.id where t2.userID is null
