LEFT JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

LEFT JOIN

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Re: LEFT JOIN

Post 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.. :?:
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

Post by sheila »

Yeah, that works. Thanks
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

care to post some sample data rows so I can play with it?
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

Post 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 :)
Post Reply