Page 1 of 1

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

Posted: Sun Sep 10, 2006 4:15 pm
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!

Posted: Sun Sep 10, 2006 4:17 pm
by feyd
hint: linked from Useful Posts.

Posted: Sun Sep 10, 2006 4:28 pm
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.

Posted: Sun Sep 10, 2006 4:30 pm
by feyd
Useful Posts is the name of the thread. It's a sticky in PHP - Code.

Posted: Sun Sep 10, 2006 4:50 pm
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!