3 Replies - 545 Views - Last Post: 23 August 2010 - 12:41 PM Rate Topic: -----

#1 auromed  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 22-May 10

Left Join, or Filtering Results from a query

Posted 22 August 2010 - 06:24 PM

I'm working on a query to select a set of questions from a table with the following criteria. I have the first part, and think I need a left join to create the correct results, but I'm obviously not doing something right.

I need a query that will return results from the questions table with the following 2 criteria.

1. question is rated less than or equal to a value provided in each of 4 subjects. (this is done via pulling the user's rank in each of the 4 categories and passing it back via PHP, so in this example I have hard set it to be '3'.

2. there is no entry in the user_questions table for this question and the user id provided. ('4' in this example) As records will only be added to this table once the user has completed it, this checks to see if the user has attempted this questions previously, and filters out those which have been attempted.

I've been able to do the first piece on its own, but am trying to add in the second criteria now, and haven't been able to fully grasp the whole Join concept. Any pointers would be appreciated.

SELECT ques_id, subject_1, subject_2, subject_3, subject_4 
FROM questions 
WHERE subject_1 <= 3 AND subject_2 <= 3 AND subject_3 <= 3 AND subject_4 <= 3 
LEFT JOIN user_questions using user_id=4 
WHERE ques_id is NULL






Database Info:

2 relevant tables to this query, questions and user_questions.

questions table details:
The table holds question text and the a difficulty in 4 categories.

Field Type Null Default Comments
ques_id int(11) No
ques_text text No
subject_1 int(11) No
subject_2 int(11) No
subject_3 int(11) No
subject_4 int(11) No

user_questions details:
This table gains an entry after a user has attempted a question.

Field Type Null Default Comments
user_id int(11) No
ques_id int(11) No

Is This A Good Question/Topic? 0
  • +

Replies To: Left Join, or Filtering Results from a query

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Left Join, or Filtering Results from a query

Posted 23 August 2010 - 06:31 AM

Your SQL is wrong. Since you are using a JOIN, you must put that in the FROM portion of your statement. Like this:

SELECT ques_id, 
subject_1, 
subject_2, 
subject_3, 
subject_4 

FROM questions LEFT JOIN user_questions on questions.ques_id=user_questions.ques_id

WHERE subject_1 <= 3 
AND subject_2 <= 3 
AND subject_3 <= 3 
AND subject_4 <= 3 
And user_id=4 
And ques_id is NULL


Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 296
  • View blog
  • Posts: 1,537
  • Joined: 07-April 08

Re: Left Join, or Filtering Results from a query

Posted 23 August 2010 - 10:32 AM

also remember that in the where clause you will need to specify which table a column is being referenced from if it exsists in both as ques_id does.

So you'd want to modify P4L's code a bit to look as such:

SELECT	questions.ques_id
	,subject_1
	,subject_2
	,subject_3
	,subject_4   
FROM 
	questions 
LEFT JOIN 
	user_questions on questions.ques_id=user_questions.ques_id  
WHERE 
	subject_1 <= 3   
	AND subject_2 <= 3   
	AND subject_3 <= 3   
	AND subject_4 <= 3   
	AND user_id=4   
	AND user_questions.ques_id is NULL 


This post has been edited by rgfirefly24: 23 August 2010 - 10:33 AM

Was This Post Helpful? 0
  • +
  • -

#4 auromed  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 22-May 10

Re: Left Join, or Filtering Results from a query

Posted 23 August 2010 - 12:41 PM

Thanks guys... I'm getting closer to what I am trying to do with each of your suggestions. I modified the code a bit as I realized I was doing the opposite of what I needed, and came up with this.

SELECT questions.ques_id, subject_1, subject_2, subject_3, subject_4, user_questions.user_id
FROM questions
LEFT JOIN user_questions ON questions.ques_id = user_questions.ques_id
WHERE subject_1 <=3
AND subject_2 <=3
AND subject_3 <=3
AND subject_4 <=3
AND (
user_id <>4
OR user_id IS NULL
)


This does work somewhat, but poses a new issue. The join, as expected adds entries for each question that fits the subject criteria, but lists the question multiple times (for questions that have been attempted by multiple users, one for each user). This will cause an issue with the question selection code because it's only expecting each question once.

This is what I end up with after that query....

ques_id	subject_1	subject_2	subject_3	subject_4	user_id
1		1			1			1			1			NULL
2		2			2			2			2			1
2		2			2			2			2			3
7		2			3			1			2			NULL
12		2			1			3			1			NULL
13		1			3			1			0			NULL
15		3			3			3			3			NULL
21		1			3			3			2			NULL
24		1			2			2			2			NULL
27		3			1			2			2			NULL
28		1			0			0			3			NULL
33		3			1			3			2			NULL
38		3			1			0			2			NULL
41		0			1			1			1			NULL
42		0			1			1			1			NULL


So, I obviously need to think about this a bit more. I only want to filter out those items which a user has already attempted, and return just the question_id, and subject information to my PHP array for processing.

Any thoughts will be appreciated.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1