sub select 1

[mickallen]

I have two tables one contains quiz scores the other contains users. When a user completes a quiz they are entered in the scores table.

Firstly I need to select user id's from the scores table based on a particular quiz. e.g.
SELECT user_id from scores where quiz_id = 13.

I then need to search the user table to find every user who hasn't completed the quiz.

Hope this makes sense

 

[sleipnir214]

What is your schema? ______________________________________________________________________
TANSTAAFL!

 

[KarveR]

its a bit vague innit.
______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[mickallen]

There are two tables, Portfolio and Users

Portfolio
User_id Score Course_id
1 20 1
2 75 1
1 50 13
3 80 1


Users
User_id Name Branch
1 jack ST
2 tom EU
3 pete ST

I need a query that will show all users by name who havn't completed course with the course_id of 13.

I can select everyone who has completed course 13 with the following query:

SELECT users. * FROM users, portfolio
WHERE users.user_id = portfolio.user_id and portfolio.course_id = 13 order by user_id;

But I can't work out how to find users who havn't done the course. I have tried a LEFT JOIN but with no luck.

Sorry for the cryptic nature of the first post.

 

[dkpede]

Why not just use:
SELECT users. * FROM users, portfolio
WHERE users.user_id = portfolio.user_id and portfolio.course_id != 13 order by user_id;

(!= means "not equal&quot ??

Best Regards ;-)

 

[carlrichey]

dkpede didn't understand the problem...

mickallen wants to bring back a list of all the users that have NOT taken course 13... although they may have taken other courses. dkpede's solution would have essentialy brought back all users because they all took course 1. The correct result set would include tom and pete but exclude jak because jack took course 13 (got a score of 50).

Certainly you would have figured it out by now but just in case:

select users.*
FROM {oj users LEFT OUTER JOIN portfolio ON
users.user_id = portfolio.user_id} ,portfolio p2
where users.user_id <> p2.user_id
and p2.course_id = 13

The trick is joining to portfolio twice. Once to bring back the full list of portfolios, and then to remove the users that had taken course 13.

Hope it helps.

Carl

 

[carlrichey]

Woops,

I told u wrong. Must be late..... This is a little cleaner.

SELECT
users.name, users.user_id
FROM
users, portfolio
LEFT JOIN portfolio p2 ON users.user_id = p2.user_id
AND portfolio.course_id = p2.course_id
WHERE
portfolio.course_id = 13
AND p2.score IS NULL

 

[mickallen]

Thanks carlrichey

That sorts it!!!!

Thanks