Page 1 of 1

A challenging MYSQL query - Any ideas

Posted: Sat Dec 10, 2005 3:54 pm
by idotcom
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.

Posted: Sat Dec 10, 2005 3:58 pm
by Burrito
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)

Posted: Sat Dec 10, 2005 4:14 pm
by trukfixer
use a left join -

select tablea.* from tablea left join tableb on (tablea.id=tableb.id) where tablea.id is null, for example

on a left join, the fields that do not match up in the larger table will leave NULL in the smaller table..

Posted: Sat Dec 10, 2005 4:51 pm
by Jenk
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:

Code: Select all

SELECT `tablea`.* FROM `tableb` LEFT JOIN `tablea` ON (`tableb`.`id`=`tablea`.`id`) WHERE [etc]

Posted: Sat Dec 10, 2005 5:16 pm
by idotcom
Thanks for your reply, but I couldn't get it to work.

Any other ideas?

Posted: Sat Dec 10, 2005 5:24 pm
by idotcom
Oops last reply was for first person that replied.


Here is my tables:


members
id

AND

posts
member_id


Not sure which table goes where.... :(

I just need all member id's from members table, all that have not posted anything in the post table.

Thanks all :D

Posted: Sat Dec 10, 2005 5:26 pm
by Burrito
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:

Code: Select all

select * from members where id not in (select member_id from posts)

Posted: Sat Dec 10, 2005 5:39 pm
by idotcom
Nope... didn't work... GRRR...

I appreciate your reply.

Maybe one of the other methods will. The join

Just need to put the right table names in the right locations.

I do have another question... does it make a difference if the two tables have different column names?


Thanks all