Member Avatar for 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

  • 2 Contributors
  • 4 Replies
  • 104 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by Scoobler
Member Avatar for 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.

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.