Joining tables and getting only unique results

[Scoobler]

Hello,

I have two tables, one with the main users details in and a users movement table which has an entry to everything they do.

I want to pull out a list of all users which havent done a specific task, put only pull the results once.

So for example I have 3 users. One user has done 10 tasks. Another 5. The other 3.

The one that has done 3 tasks, one of which adds the action "Completed".

I want to get a list of all users which have NOT got an entry in the movements table with "completed" in the "action" column.

Also, I only want each user once. Instead of the users details for each movement entry.

I hope this makes sense.

P.S. I am using PHP and MySQL.

Thanks in advance for any help!

Jamie

[blocblue]

Hey Jamie,

Based on what you've said...

You want to select the details of users who do not have "completed" in the action column for one or more tasks. Hence, your user with three tasks, they should still appear in the results, because they have nothing in the action column for the other two tasks? Correct?

SELECT *
FROM `users`
WHERE `uid` IN (
[INDENT]SELECT DISTINCT `uid`
FROM `movements`
WHERE `action` <> 'completed'[/INDENT]
)

Is that what you were after??

R.

[Scoobler]

Hi robothy,

Thanks for the reply, its not quite what I'm after, but closer, basically if a user has completed a task, I don't want to see them.

So from my earlier example, the user who has 10 tasks (but not completed) should be in the results once. As should the user who has done 5 tasks (but not completed). The final user, who has completed in 3 tasks, I don't want to see them in the results.

Thanks again for your help so far!

Jamie

[blocblue]

Oh right, okay.

Well what I wrote before will find the user id of any user who has incomplete tasks. Regardless of how many.

R.

[Scoobler]

Hi robothy,

Thanks for the query, I hadn't had chance to try it when I posted earlier. I have since changed it round a little and its perfect thanks!

SELECT *
FROM users
WHERE u_id IN (
	SELECT DISTINCT u_id 
	FROM movements)
AND u_id NOT IN (
	SELECT DISTINCT u_id 
	FROM movements 
	WHERE action = "Completed");

Now I can select all all users who have taken part in an activity but have not yet completed the activity.

Thanks again!

Jamie.