Re: [PHP-DB] selecting rows with no reference

[Brent Baisley]

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

RE: [PHP-DB] selecting rows with no reference

[Bastien Koert]

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