[SOLVED] MySQL - JOIN

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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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;
RadixDev
Forum Commoner
Posts: 66
Joined: Sun Mar 14, 2004 11:27 am
Location: U.K.

Post 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?
Last edited by RadixDev on Tue Jul 20, 2004 6:22 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
RadixDev
Forum Commoner
Posts: 66
Joined: Sun Mar 14, 2004 11:27 am
Location: U.K.

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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;
RadixDev
Forum Commoner
Posts: 66
Joined: Sun Mar 14, 2004 11:27 am
Location: U.K.

Post by RadixDev »

Cheers ! :D
ammd
Forum Newbie
Posts: 4
Joined: Thu Mar 30, 2006 11:57 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why did you resurrect a two year old thread that was already solved?
ammd
Forum Newbie
Posts: 4
Joined: Thu Mar 30, 2006 11:57 am

Post 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.
Post Reply