RE: [PHP-DB] selecting rows with no reference
is the user id still there? if not, an outer join should be what you need to use to get all the rows where there is no match in the other table bastien From: Merlin <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [PHP-DB] selecting rows with no reference Date: Wed, 08 Dec 2004 12:30:34 +0100 Hi there, I know this is a question for the mysql mailing list, but maybe one of you guys also could help out with that. I do have to tables (users, messages). Now there unfortunatelly are some messages where the sender in the user table is missing. I am trying to find all messages which do not have the belonging user anymore. So I tried this: SELECT m.subject, m.status FROM user u, messages m WHERE m.sender_user_id != u.user_id But this returns far to many rows. Has anybody an idea how to do that? Thank you in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] selecting rows with no reference
This is probably what you are looking for: SELECT m.subject,m.status,u.user_id FROM messages m LEFT JOIN user u ON m.sender_user_id=u.user_id WHERE u.user_id IS NULL That will give you all message without a related user. On Dec 8, 2004, at 6:30 AM, Merlin wrote: Hi there, I know this is a question for the mysql mailing list, but maybe one of you guys also could help out with that. I do have to tables (users, messages). Now there unfortunatelly are some messages where the sender in the user table is missing. I am trying to find all messages which do not have the belonging user anymore. So I tried this: SELECT m.subject, m.status FROM user u, messages m WHERE m.sender_user_id != u.user_id But this returns far to many rows. Has anybody an idea how to do that? Thank you in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] selecting rows with no reference
Hi there, I know this is a question for the mysql mailing list, but maybe one of you guys also could help out with that. I do have to tables (users, messages). Now there unfortunatelly are some messages where the sender in the user table is missing. I am trying to find all messages which do not have the belonging user anymore. So I tried this: SELECT m.subject, m.status FROM user u, messages m WHERE m.sender_user_id != u.user_id But this returns far to many rows. Has anybody an idea how to do that? Thank you in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php