Page 1 of 1

SQL SELECT JOIN HELP

Posted: Thu Jan 06, 2005 3:44 am
by Pyrite
Hey guys, you all know how much I suck at SQL. I am trying to get all records from one table that don't have records in another table with matching keys. I think what I want is a LEFT OUTER JOIN, but I am getting the results that are opposite that I want.

Code: Select all

SELECT  DISTINCT gop_id, gop_option
FROM tbl_gradeoptions 
LEFT JOIN tbl_customgrades ON gop_id = cgr_optionid
WHERE ( cgr_classid =  '33' )
Here is what tbl_gradeoptions looks like.
gop_id (primary key)
gop_option (some text)

And tbl_customgrades has:
cgr_id (primary key)
cgr_classid (foreign key)
cgr_optionid (foreign key to gop_id)
cgr_percentage (some more text)

Now I want to get all records from tbl_gradeoptions that don't have records with matching keys in tbl_customgrades (gop_id and cgr_optionid) and also where the classid is 33.

Now my query gives me the opposite of what I want, so I know I am sorta on my way there. Help? :roll:

Example:

tbl_gradeoptions
gop_id - gop_option
1 - Apple
2 - Pear
3 - Grape
4 - Durian

tbl_customgrades
cgr_id - cgr_classid - cgr_optionid - cgr_percent
1 - 33 - 3 - nine
2 - 32 - 2 - eight
3 - 52 - 4 - five
4 - 33 - 4 - two

So selecting from the first table, i would want to get Apple & pear only.

Posted: Thu Jan 06, 2005 8:18 am
by feyd

Posted: Thu Jan 06, 2005 10:06 am
by Pyrite
Ok, a little bit confusing, but I'll give it a go and see if i can figure it out. Thanks.

Posted: Thu Jan 06, 2005 10:08 am
by feyd
if you start from the beginning of the thread (remove the start=3 bit) it may explain what's going on a bit more. My explaination of how it works later may shed some light on it too.

Posted: Thu Jan 06, 2005 10:26 pm
by Pyrite
Ok if I just do

Code: Select all

SELECT  gop_id, gop_option
FROM tbl_gradeoptions
LEFT JOIN tbl_customgrades ON gop_id = cgr_optionid
WHERE  (cgr_optionid IS NULL)
It works. Returns the results from gradeoptions that aren't being used in the other table. But if I also add "AND (cgr_classid = '33')" it returns no records.

Posted: Thu Jan 06, 2005 10:29 pm
by feyd
can you post the table structures?

Posted: Thu Jan 06, 2005 10:39 pm
by Pyrite
Ok here ya go.

Code: Select all

CREATE TABLE `tbl_customgrades` (
  `cgr_id` bigint(20) NOT NULL auto_increment,
  `cgr_classid` bigint(20) NOT NULL default '0',
  `cgr_optionid` smallint(6) NOT NULL default '0',
  `cgr_percentage` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`cgr_id`)
) TYPE=InnoDB;

CREATE TABLE `tbl_gradeoptions` (
  `gop_id` int(11) NOT NULL auto_increment,
  `gop_facid` bigint(20) NOT NULL default '0',
  `gop_langid` tinyint(4) NOT NULL default '0',
  `gop_option` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`gop_id`)
) TYPE=InnoDB;

Posted: Thu Jan 06, 2005 10:44 pm
by feyd
okay.. cgr_optionid is required to be NULL.. which means all other fields from that table are NULL. So asking for rows where cgr_classid is 33 would be none of the rows.

I'm pretty sure this will be what you want though:

Code: Select all

SELECT  gop_id, gop_option
FROM tbl_gradeoptions
LEFT JOIN tbl_customgrades ON gop_id = cgr_optionid AND cgr_classid = 33
WHERE  (cgr_optionid IS NULL)

Posted: Thu Jan 06, 2005 10:48 pm
by Pyrite
Yes it does thank you! Didn't know you can use "AND" on the JOIN part. But should I modify my table structure to allow NULL values? What is the advantage?

Posted: Thu Jan 06, 2005 10:50 pm
by feyd
no you don't need to.. the LEFT/RIGHT JOIN when it fails to find a match sets all fields to NULL, as the results are arbitrary to the fields used (basically)

Posted: Thu Jan 06, 2005 10:57 pm
by Pyrite
Dude, you da man! One more question. How in the hell did you learn SQL so well? I have like zero friends that even know basic SQL (hard to ask for help). I want to learn SQL and be good at it, but I have serious trouble understanding the concepts of JOINS and other things too.

Posted: Thu Jan 06, 2005 11:00 pm
by markl999
Don't wish to butt in but a good book and http://www.sqlcourse.com/ (and http://www.sqlcourse2.com/) helped me a lot.

Posted: Thu Jan 06, 2005 11:13 pm
by feyd
literally... when someone asked a question here regarding it. Since I knew little about it as well when I started in PHP (about a year ago) I asked questions to a co-worker who knew SQL more than PHP, whereas I could grasp the concepts of PHP very quickly. So we collectively shared some. When I found phpDN I would attempt to solve the problems that people would come here and ask on my own. I spent many hours just screwing around with different queries until they just started to gel. The joins were by far the most difficult.. but once I understood the basic concepts, they became very easy.

If you'd like, I can try to explain them more, but the dirt basics are: LEFT JOIN works in a left handed direction; the tables on the right connect to the tables on the left in some fashion, if at all. RIGHT JOIN works in a right handed direction; the tables on the left connect to the tables on the right in some fashion. Very similar sounding, except that the places that don't connect are nullified. INNER JOIN requires that there is a connection to return a result. NATURAL JOIN has no ON parameters as it'll automatically join any fields named the same between the two tables it's connected with.