A challenging MYSQL query - Any ideas

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

A challenging MYSQL query - Any ideas

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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)
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post 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..
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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]
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Post by idotcom »

Thanks for your reply, but I couldn't get it to work.

Any other ideas?
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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)
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Post 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
Post Reply