Page 1 of 1
MySQL - JOIN
Posted: Tue Jul 20, 2004 3:09 pm
by RadixDev
I have the following MySQL query:
Code: Select all
SELECT group_name FROM rdxls_groups JOIN rdxls_banned WHERE rdxls_groups.group_name!=rdxls_banned.data
Basically "rdxls_banned" table has the name of the banned group in a field called "data". What I'm trying to do is to get the group name from the table "rdxls_groups" which are not found in the "rdxls_banned" table.
But when I execute the above query it gives me the results I want but x3, the same group name is returned three times... Any ideas?
Posted: Tue Jul 20, 2004 3:20 pm
by liljester
SELECT DISTINCT(group_name) ...
Posted: Tue Jul 20, 2004 3:32 pm
by RadixDev
OK... it stopped returning lots of results but I realised the query also returns the banned group. when you join a table is it joined according to the id numbers. so if you have id 1 record in one table it get joint with id 1 record in the other table. If so how would not make it so taht id 1 record in on table gets joint with all records in the other table? thanks again for the quick reply btw
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 !

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.