PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Feb 18, 2019 2:37 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: SQL SELECT JOIN HELP
PostPosted: Thu Jan 06, 2005 4:44 am 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
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.

Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 9:18 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
http://forums.devnetwork.net/viewtopic. ... 04&start=3


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:06 am 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
Ok, a little bit confusing, but I'll give it a go and see if i can figure it out. Thanks.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:08 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:26 pm 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
Ok if I just do

Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:29 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
can you post the table structures?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:39 pm 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
Ok here ya go.

Syntax: [ Download ] [ Hide ]
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;


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:44 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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:
Syntax: [ Download ] [ Hide ]
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)


Last edited by feyd on Mon Aug 15, 2005 5:53 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:48 pm 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
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?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:50 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 06, 2005 11:57 pm 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 07, 2005 12:00 am 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)
Don't wish to butt in but a good book and http://www.sqlcourse.com/ (and http://www.sqlcourse2.com/) helped me a lot.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 07, 2005 12:13 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group