LEFT JOIN
Posted: Wed Feb 22, 2006 12:21 pm
I'm having trouble getting LEFT JOIN to work as expected.
The application tracks advancement for boy scouts.
I have a table that lists all the requirements for each rank and another
that records the date a particular scout (user_id) completes a requirement.
Here are some queries on the tables and what I get back
returns 22 rows, requirement_id 28-49
returns 47 rows, requirement_id 1-46,48
meaning this scout has completed all requirements for rank_id=1 and 2,
and all but two for rank_id=3, the missing two are 47 and 49
What I want is a list of all requirments for a given rank_id and user_id along with the date_earned.
If the requirement hasn't been completed then I want date_earned to be null.
Here's what I'm trying
returns 20 rows, requirement_ids 47 and 49 are missing
Anybody see why this doesn't work?
I'm using MySQL 3.23.56 but I've also tried 4.1.12 and get the same results.
The application tracks advancement for boy scouts.
I have a table that lists all the requirements for each rank and another
that records the date a particular scout (user_id) completes a requirement.
Code: Select all
CREATE TABLE `requirement` (
`requirement_id` int(11) NOT NULL auto_increment,
`rank_id` int(11) NOT NULL default '0',
`number` tinyint(4) default NULL,
`subnumber` char(1) default NULL,
`description` text,
`short_desc` varchar(30) default NULL,
PRIMARY KEY (`requirement_id`),
KEY `rank_id` (`rank_id`)
) TYPE=MyISAM;
CREATE TABLE `scout_req` (
`scout_req_id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`requirement_id` int(11) NOT NULL default '0',
`date_earned` date default NULL,
`modified` timestamp(14) NOT NULL,
PRIMARY KEY (`scout_req_id`),
KEY `user_id` (`user_id`),
KEY `requirement_id` (`requirement_id`)
) TYPE=MyISAM;Here are some queries on the tables and what I get back
Code: Select all
SELECT requirement_id, rank_id, number, subnumber, short_desc
FROM requirement
WHERE rank_id = '3'Code: Select all
SELECT requirement_id, date_earned
FROM scout_req
WHERE user_id = '2'meaning this scout has completed all requirements for rank_id=1 and 2,
and all but two for rank_id=3, the missing two are 47 and 49
What I want is a list of all requirments for a given rank_id and user_id along with the date_earned.
If the requirement hasn't been completed then I want date_earned to be null.
Here's what I'm trying
Code: Select all
SELECT r.requirement_id, rank_id, number, subnumber, short_desc, date_earned
FROM requirement r
LEFT JOIN scout_req s ON ( r.requirement_id = s.requirement_id )
WHERE s.user_id = '2' AND rank_id = '3'Anybody see why this doesn't work?
I'm using MySQL 3.23.56 but I've also tried 4.1.12 and get the same results.