Hi there.
I have lost some of my query construction skills since not writing any for a while. I was hoping someone could help me build one.
I need a query that will select member id's from table A if there member id does not exist in table B
something like:
select member id from members where member id does not exist in posts
Any ideas?
Thanks in advance.
A challenging MYSQL query - Any ideas
Moderator: General Moderators
if you have mysql 4.1 or higher you can use sub queries:
Code: Select all
select memberid from tableA where memberid not in (select memberid from tableB)Left join means all rows will be returned from the first table, whilst only the rows that satisfy the join criteria will be returned from the second table.
In your example, all rows will be returned from tablea
Change to this:
In your example, all rows will be returned from tablea
Change to this:
Code: Select all
SELECT `tablea`.* FROM `tableb` LEFT JOIN `tablea` ON (`tableb`.`id`=`tablea`.`id`) WHERE [etc]well what I suggested will only work if you have MySQL 4.1 or higher (as I mentioned). If you're using a prior version, you should look at some different join types as suggested by subsequent posters.
if you do have 4.1 or greater try this:
if you do have 4.1 or greater try this:
Code: Select all
select * from members where id not in (select member_id from posts)