DISTINCT Problems

ZibingsCoder

Been a while since I've posted on here, but, I've run into a problem that I just -cannot- seem to get past. Here's the breakdown...

I am modifying a version of ATutor for one of our clients. ATutor does not do a very good job of linking tests to their relevant content-tree. Even so, said client has decided that they want to be able to receive reports of who has completed their ethics training course.

We have decided that the best way to determine whether or not a person has finished their ethics course, is to check for the existance of an entry in a test_result table, correlating to a specific user (member_id) and a test (test_id). The test_id we're looking for, is test_id 20. Member_id is not really important, as there are close to 7,000 users that are taking the test.

The easy part is getting the list to show whether or not someone has finished the course. It was even easy compiling the list of people who have completed the test/course.

I'm having a problem though, creating a list of people who have not completed the course. I only have MySQL 4.0.25, so I can't do subqueries sadly. There are more than just the test_id=20 test, and everytime someone takes it, there is another added entry for their member_id in the test_results table.

[Point]
The point of this, again, is that I want to know if there is a way of doing this entirely in the database. I need to test to see if a distinct member_id does NOT have an entry for test_id=20 in the test_results database. I need to be able to pull the user's information, and use that towards a LIMIT count.
[/Point]

I can do all of this with PHP, I know. However, I was hoping to avoid it as much as possible, as that would involve rewriting close to 1,000 lines of code, and I need this done within an hour or two...or three if I must. I would like to go home sometime tonight. 😉

Thanks in advance for any help.

Andy

bubblenut

I can think of one way. You have the users table right and the test_result table. To find all the users who have passed the test you straight join the users table onto the test_result table along with the condition that test_id must be equal to 20, right?

To do the reverse you left join the test_result table onto the user table along with the condition that the test_id must be 20 ( in the join condition) and then in the main where condition you make the condition that the id field for the test_result table must be null, ie there was nothing in the test_result table to join. It's a bit of a messy hack but I've used the same concept before with good results, although not in quite this situation though.

SELECT users.id, users.name 
FROM users
LEFT JOIN test_results ON (test_results.user=users.id && test_results.test_id=20)
WHERE test_results.id IS NULL;

If I've got the wrong end of the stick could you provide the DB scheme to help me understad what you're after please?

ZibingsCoder

Hey bubblenut! Nice to hear from you, and thanks for the help so far.

I may not have explained this or even mentioned it at all, but the problem with your query, is that it depends on the test_result entry existing, and existing without a test_id in it. I'm going to post the relevant schemas here in a minute, but, let me be a little plainer about what I am trying to do...

I need to find out, which users, haven't taken test #20. Test #20 is one of about 100 tests in the database. When they take the test, an entry is inserted into test_results, which then allows the program to see that they've taken it before. This entry sits alongside any other test entries that they have taken.

I need to be able to find out who does not have an entry for test #20, and get a list of their user data.

Here are the schemas, I hope this explanation was a little better.

CREATE TABLE `AT_members` (
  `member_id` mediumint(8) unsigned NOT NULL auto_increment,
  `login` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `website` varchar(200) NOT NULL default '',
  `first_name` varchar(100) NOT NULL default '',
  `last_name` varchar(100) NOT NULL default '',
  `dob` date NOT NULL default '0000-00-00',
  `gender` enum('m','f') NOT NULL default 'm',
  `address` varchar(255) NOT NULL default '',
  `postal` varchar(15) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `province` varchar(50) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `phone` varchar(15) NOT NULL default '',
  `status` tinyint(4) NOT NULL default '0',
  `preferences` text NOT NULL,
  `creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `language` varchar(5) NOT NULL default '',
  `inbox_notify` tinyint(3) unsigned NOT NULL default '0',
  `location_code` varchar(25) default NULL,
  `supervisor_name` varchar(80) default NULL,
  `sup_id` varchar(10) default NULL,
  `sup_email` varchar(100) default NULL,
  `source_code` varchar(25) default NULL,
  `start_date` varchar(20) default NULL,
  `hr_rep_name` varchar(100) default NULL,
  `hr_mgr_name` varchar(100) default NULL,
  `assignment_status` varchar(100) default NULL,
  PRIMARY KEY  (`member_id`),
  UNIQUE KEY `login` (`login`)
) TYPE=MyISAM;


CREATE TABLE `AT_tests_results` (
  `result_id` mediumint(8) unsigned NOT NULL auto_increment,
  `test_id` mediumint(8) unsigned NOT NULL default '0',
  `member_id` mediumint(8) unsigned NOT NULL default '0',
  `date_taken` datetime NOT NULL default '0000-00-00 00:00:00',
  `final_score` char(5) NOT NULL default '',
  PRIMARY KEY  (`result_id`),
  KEY `test_id` (`test_id`)
) TYPE=MyISAM;

Here's hoping this will help a bit.

Thanks again!

bubblenut

ZibingsCoder wrote:
I may not have explained this or even mentioned it at all, but the problem with your query, is that it depends on the test_result entry existing, and existing without a test_id in it.

No it doesn't, it's a LEFT join. Therefore all rows in users will be returned regardless. and then only those for which a record was not found in the test_result table will be displayed. I'm even more convinced than before that this is what you're looking for now.

ZibingsCoder

Alright, well the reason I told you that there was this problem with it, is because I tried it before posting a reply. Here is the query I used...

$sql = "SELECT *, DATE_FORMAT(start_date, '%m/%d/%y %h:%i %p') AS start_date FROM {$memberTable} LEFT JOIN AT_tests_results ON (AT_tests_results.member_id=AT_members.member_id AND AT_tests_results.test_id=20) WHERE AT_tests_results.test_id IS NULL ORDER BY last_name, first_name DESC, creation_date DESC {$limiter}";

If you'd like, I can PM you the url for the live version of this so you can see the query in action. Did I maybe do the query wrong?