MySQL - select all records where ID not in 2nd table?

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
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

MySQL - select all records where ID not in 2nd table?

Post by fgomez »

Hello,

I have a couple of tables I use for processing free literature requests. One of them (let's call it USERS) holds user information, and the other holds info about what they requested (REQ). Unfortunately, I made a syntax error which prevented data from being recorded in the second table. Fortunately, the error did not affect the recording of the users' contact information.

Each user has an id, which also appears in the REQ table to tie orders to users.

What I want to do is find all records in USERS for which REQ has no record, using the user id. I can't think of what this query would look like, though.

In other words, USERS looks like this:

ID | EMAIL
1 | test@example.com
2 | you@domain.com
3 | mail@nowhere.com
4 | foo@bar.com
5 | yada@yada.net

... and REQ looks like this
U_ID | ITEM
1 | 101
2 | 142
5 | 112

What kind of query could I write that would return the email addresses for only IDs 3 and 4?

Thanks for your help!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hint: linked from Useful Posts.
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

feyd wrote:hint: linked from Useful Posts.
Thanks, Feyd. I looked at "Sticky: Debugging MySQL code .. a few tips." and "Sticky: Useful Resources" but couldn't find what you were getting at.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Useful Posts is the name of the thread. It's a sticky in PHP - Code.
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

feyd wrote:Useful Posts is the name of the thread. It's a sticky in PHP - Code.
Thanks, Feyd. This was a big help. Gah, my little syntax error affected 72 requests! Guess I'll loop them through a mail() function...

For anyone else who is reading this, the post that helped me is at: viewtopic.php?t=29151.

Good luck, and don't make stupid syntax errors!
Post Reply