Select From `table1` Where NOT in `table2` ?? [Solved]

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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Select From `table1` Where NOT in `table2` ?? [Solved]

Post by Skara »

I've two tables, basically like this:

images: id, otherinfo...

tags: id, imageid, otherinfo...

Now, I want to select everything in `images` that doesn't have an entry in `tags`
How might I go about that?
Such as...

SELECT * FROM `images` WHERE `images`.`id` is not in `tags`.`imageid`;

Is this easily done? Or should I add another row in the `images` table that says whether or not there have been tags added yet (big hassle)?
Last edited by Skara on Wed Mar 21, 2007 8:16 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Maybe something like:

SELECT * FROM `images` LEFT JOIN tags ON images.id=tags.imageid WHERE tags.imageid IS NULL;
(#10850)
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

Aha! I had to change the *, though. As your query stands, for some reason images.id doesn't get selected--it winds up empty.
This works, though:

Code: Select all

SELECT `images`.`id`,`images`.`file`,`images`.`rating` FROM `images` LEFT JOIN tags ON images.id = tags.fileid WHERE tags.fileid IS NULL;
Thanks!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Skara wrote:for some reason images.id doesn't get selected--it winds up empty.
When joining, if you have two fields with the same name, use AS to give one of them a different name.
(#10850)
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

Ah.. ok. Thanks.
Post Reply