Page 1 of 1

LEFT JOIN

Posted: Wed Feb 22, 2006 12:21 pm
by sheila
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.

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'
returns 22 rows, requirement_id 28-49

Code: Select all

SELECT requirement_id, date_earned
FROM scout_req
WHERE user_id =  '2'
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

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'
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.

Re: LEFT JOIN

Posted: Wed Feb 22, 2006 12:44 pm
by feyd

Code: Select all

SELECT
	* 
FROM
	`requirement`
LEFT JOIN
	`scout_req`
	ON
		`scout_req`.`requirement_id` = `requirement`.`requirement_id`
WHERE
	`rank_id` = 3
	AND
	(
		`user_id` = 2
		OR
		`user_id` IS NULL
	)
maybe.. :?:

Posted: Wed Feb 22, 2006 12:50 pm
by sheila
Yeah, that works. Thanks

Posted: Wed Feb 22, 2006 2:03 pm
by sheila
Spoke too soon. It worked until I added more records for other scouts to scout_req.

For example, user_id = 2 has completed all requirements for rank_id = 1 and user_id = 3 has completed some of the requirements for rank_id = 1. Querying for rank_id = 1 and (user_id = 3 or user_id is null) returns just the completed requirements of user_id = 3

Any other ideas? Is there any way to do this with one query or do I need to do two, one to get the requirements for a rank and then another to look for matches in scout_req?

Posted: Wed Feb 22, 2006 2:08 pm
by feyd
care to post some sample data rows so I can play with it?

Posted: Wed Feb 22, 2006 2:32 pm
by sheila
From requirement table, rank_id =1 and rank_id 2, I've shorten the contents of the description field

Code: Select all

INSERT INTO `requirement` VALUES (1, 1, 1, '', 'Meet age requirements... ', 'Age');
INSERT INTO `requirement` VALUES (2, 1, 2, '', 'Complete a Boy Scout application ... .', 'Application');
INSERT INTO `requirement` VALUES (3, 1, 3, '', 'Find a Scout troop near your home.', 'Find Troop');
INSERT INTO `requirement` VALUES (4, 1, 4, '', 'Repeat the Pledge of Allegiance.', 'Pledge of Allegiance');
INSERT INTO `requirement` VALUES (5, 1, 5, '', 'Demonstrate the Scout sign, salute, and handshake.', 'Scout Sign');
INSERT INTO `requirement` VALUES (6, 1, 6, '', 'Demonstrate tying the square knot (a joining knot).', 'Square Knot');
INSERT INTO `requirement` VALUES (7, 1, 7, '', 'Understand and agree to live by ....', 'Live Scout Oath');
INSERT INTO `requirement` VALUES (8, 1, 8, '', 'Describe the Scout badge.', 'Describe badge');
INSERT INTO `requirement` VALUES (9, 1, 9, '', 'Complete the pamphlet exercises. ...', 'Pamplet Exercises');
INSERT INTO `requirement` VALUES (10, 1, 10, '', 'Participate in a Scoutmaster conference... ', 'Scoutmaster Conf.');
INSERT INTO `requirement` VALUES (11, 2, 1, '', 'Present yourself to your leader... ', 'Present at campout');
INSERT INTO `requirement` VALUES (12, 2, 2, '', 'Spend at least one night .... ', 'Sleep in Tent');
INSERT INTO `requirement` VALUES (13, 2, 3, '', 'On the campout, assist in ... ', 'Help cook');
INSERT INTO `requirement` VALUES (14, 2, 4, 'a', 'Demonstrate how to whip and fuse the ends of a rope.', 'Whip, Fuse');
INSERT INTO `requirement` VALUES (15, 2, 4, 'b', 'Demonstrate that you know ... ', 'Tie Knots');
INSERT INTO `requirement` VALUES (16, 2, 5, '', 'Explain the rules of safe hiking...  ', 'Explain Safe Hike');
INSERT INTO `requirement` VALUES (17, 2, 6, '', 'Demonstrate how to display, ... ', 'Display Flag');
INSERT INTO `requirement` VALUES (18, 2, 7, '', 'Repeat from memory and explain ... ', 'Oath, Law, etc.');
INSERT INTO `requirement` VALUES (19, 2, 8, '', 'Know your patrol name, ... ', 'Patrol yell');
INSERT INTO `requirement` VALUES (20, 2, 9, '', 'Explain why we use the buddy system in Scouting.', 'Buddy system');
INSERT INTO `requirement` VALUES (21, 2, 10, 'a', 'Record your best in the following ... ', 'Exercises');
INSERT INTO `requirement` VALUES (22, 2, 10, 'b', 'Show improvement in the activities ... ', 'Improved Exercises');
INSERT INTO `requirement` VALUES (23, 2, 11, '', 'Identify local poisonous plants; ... ', 'Poisonous plants');
INSERT INTO `requirement` VALUES (24, 2, 12, 'a', 'Demonstrate the Heimlich maneuver... ', 'Heimlich maneuver');
INSERT INTO `requirement` VALUES (25, 2, 12, 'b', '<ul>Show first aid for the following: ... ', 'First Aid');
INSERT INTO `requirement` VALUES (26, 2, 13, '', 'Participate in a Scoutmaster conference. ', 'Scoutmaster Conf.');
INSERT INTO `requirement` VALUES (27, 2, 14, '', 'Complete your board of review. ', 'Board of Review');
and for scout_req, records for user_id 2 and 3

Code: Select all

INSERT INTO `scout_req` VALUES (1, 2, 10, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (2, 2, 9, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (3, 2, 8, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (4, 2, 7, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (5, 2, 6, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (6, 2, 5, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (7, 2, 4, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (8, 2, 3, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (9, 2, 2, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (10, 2, 1, '2002-02-25', 20060222095211);
INSERT INTO `scout_req` VALUES (11, 2, 27, '2002-09-07', 20060222095237);
INSERT INTO `scout_req` VALUES (12, 2, 26, '2002-09-07', 20060222095237);
INSERT INTO `scout_req` VALUES (13, 2, 25, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (14, 2, 24, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (15, 2, 23, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (16, 2, 22, '2002-09-07', 20060222095237);
INSERT INTO `scout_req` VALUES (17, 2, 21, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (18, 2, 20, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (19, 2, 19, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (20, 2, 18, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (21, 2, 17, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (22, 2, 16, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (23, 2, 15, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (24, 2, 14, '2002-08-07', 20060222095237);
INSERT INTO `scout_req` VALUES (25, 2, 13, '2002-04-19', 20060222095237);
INSERT INTO `scout_req` VALUES (26, 2, 12, '2002-04-19', 20060222095237);
INSERT INTO `scout_req` VALUES (27, 2, 11, '2002-04-19', 20060222095237);
INSERT INTO `scout_req` VALUES (60, 3, 1, '2005-02-25', 20060222134829);
That should represent user_id = 2 completing all requirements for both rank_id =1 and 2, and user_id = 3 completed one requirement only.

Have fun :)