Page 1 of 1

Posted: Tue Jul 20, 2004 3:34 pm
by feyd
unfortunately that wouldn't exactly solve it..

Code: Select all

SELECT t1.`group_name` FROM `rdxls_groups` t1 LEFT JOIN `rdxls_banned` t2 ON t1.`group_name` = t2.`group_name` WHERE t2.`group_name` IS NULL;

Posted: Tue Jul 20, 2004 6:14 pm
by RadixDev
It works!!! Thanks but how does it work? Sorry and another thing, I also need to make sure, in the query, that when comparing rdxls_banned.data and rdxls_groups.group_name, rdxls_banned.data - the same record must also satisfy, rdxls_banned.type='group'. How do i do that?

Posted: Tue Jul 20, 2004 6:17 pm
by feyd
LEFT JOIN looks at the data to it's left, heh, and if it doesn't match, all fields that'd normally be set, are set to NULL. So you filter it where those rows are null.. where the tables don't match..

Posted: Wed Jul 21, 2004 3:29 am
by RadixDev
RadixDev wrote:It works!!! Thanks but how does it work? Sorry and another thing, I also need to make sure, in the query, that when comparing rdxls_banned.data and rdxls_groups.group_name, rdxls_banned.data - the same record must also satisfy, rdxls_banned.type='group'. How do i do that?

Posted: Wed Jul 21, 2004 4:50 am
by feyd
not entirely sure but:

Code: Select all

SELECT t1.`group_name` FROM `rdxls_groups` t1 LEFT JOIN `rdxls_banned` t2 ON t1.`group_name` = t2.`group_name` AND t2.`type` = 'group' WHERE t2.`group_name` IS NULL;

Posted: Wed Jul 21, 2004 7:34 am
by RadixDev
Cheers ! :D

Posted: Thu Mar 30, 2006 4:43 pm
by ammd
Every time I want to select records from a table that meet the condition of certain value not being in another table I use NOT IN (SELECT).

Let's say I have a validation table for country codes called COUNTRY with the following fields:
CountryCode
CountryDescription

Then I have a table of users called USERS, that among other columns, includes one for country code:
UserNumber
UserName
UserCountryCode

I want to find the records in table USERS that have a UserCountryCode not matching CountryCode from COUNTRY. This is the query I would write:

Code: Select all

SELECT * 
FROM USERS
WHERE UserCountryCode NOT IN 
     (SELECT CountryCode FROM COUNTRY)
Hope this helps.

Posted: Thu Mar 30, 2006 5:04 pm
by feyd
why did you resurrect a two year old thread that was already solved?

Posted: Thu Mar 30, 2006 5:23 pm
by ammd
I'm sorry, I didn't notice that it was so old. I actually got to it from a list of "useful posts" and just read that it was from "March" but didn't notice the year . I apologize.