0

I'm working on a project and I need to get some data from database. First of all I have activities and they depend on a day of a planification. I want to award users who have done the activities the first week, for exemple.

In order to get the activities of the first week I'm doing this SQL:

SELECT activitat
FROM planning_activitats
WHERE (activitat <> 'CTS' AND activitat <> 'TRU' AND activitat <> 'Test')
  AND dia >= 1 AND dia <= 7

This query is correct and I get all the activities in a correct way. Ok, now I want to know what users have done these activities and I do this SQL: 

SELECT user
FROM activitats_completades
WHERE activitat = ALL ($sql)
  AND user NOT IN (SELECT user FROM puntuacions_setmanals WHERE setmana = 1)

Where $sql is the first SQL. The second part of WHERE (AND user NOT IN...) is because I need to manage what users have been awarded and they can't be awarded another time.

I think the problem is in ALL operator because I'm doing the query and I'm getting 0 results. What I'm doing bad? I have checked database and data is correct and it should give me some user because I have users who have done all the activities.

Thanks for your help.

EDIT: Data samples -> User "user" have done activities "ACT1, ACT2, ACT3", "user2" have done "ACT1, ACT4" and this week activities are "ACT1, ACT4". In this case, "user2" should be the result.

Improve this question
9
  • One value cannot be equal to all of several different values. For illustration - try to find a number which is = ALL (3, 5, 7). Did you mean WHERE activitat IN ($sql)? Commented Oct 1, 2018 at 11:47
  • 1
    Sample data and desired results would really help. Commented Oct 1, 2018 at 11:51
  • @Amadan in the first query I get, for example, ACT1, ACT2 and ACT3. And I want to get users that have done the 3 activities, how can I do it? I understand you, but how can I solve it? I'm using PHP but i prefer not to work with arrays, but if it's the solution... Is possible to solve it with SQL queries? Commented Oct 1, 2018 at 11:59
  • I don't want to try to start making queries since you haven't provided the schema; but if you want to make sure a user has done 3 activities, you need to COUNT results of a subquery or group and make sure he has the requisite number. Commented Oct 1, 2018 at 12:01
  • @Amadan yes but it's not the solution because I need to check the name of activities, not the number of them becasue the number always will be the same (5 activities for week) Commented Oct 1, 2018 at 12:03

4 Answers 4

Reset to default
1

Here is something that should solve your problem. It is what I spoke of before, but in pretty and usable:

SELECT user
FROM activitats_completades
WHERE activitat IN (
    SELECT activitat
    FROM planning_activitats
    WHERE (activitat <> 'CTS' AND activitat <> 'TRU' AND activitat <> 'Test')
    AND dia >= 1 AND dia <= 7
)
AND user NOT IN (SELECT user FROM puntuacions_setmanals WHERE setmana = 1)
GROUP BY user
HAVING COUNT(activitat) = (
    SELECT COUNT(activitat)
    FROM planning_activitats
    WHERE (activitat <> 'CTS' AND activitat <> 'TRU' AND activitat <> 'Test')
    AND dia >= 1 AND dia <= 7
)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@msabate Here you will find users 0 and 1 who made both "foo" and "bar" activities and user 3 who just made "foo". The rest did none of them. As the result of the query you find just users 0 and 1.
@msabate Now I saw the mistake... I did not remove the ALL operator when copying your query.
Thanks for your contribution
0

Use IN or EXISTS:

SELECT ac.user
FROM activitats_completades ac
WHERE ac.activitat IN (SELECT pa.activitat
                       FROM planning_activitats pa
                       WHERE pa.activitat NOT IN ('CTS', 'TRU', 'Test') AND
                             pa.dia >= 1 AND pa.dia <= 7
                      ) AND
     ac.user NOT IN (SELECT ps.user FROM puntuacions_setmanals WHERE ps.setmana = 1);

There is no need to save the returned values in a variable.

To get users that match all the activities, use GROUP BY and HAVING:

SELECT ac.user
FROM activitats_completades ac
WHERE ac.activitat IN (SELECT pa.activitat
                       FROM planning_activitats pa
                       WHERE pa.activitat NOT IN ('CTS', 'TRU', 'Test') AND
                             pa.dia >= 1 AND pa.dia <= 7
                      ) AND
     ac.user NOT IN (SELECT ps.user FROM puntuacions_setmanals WHERE ps.setmana = 1)
GROUP BY ac.user
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM planning_activitats pa
                   WHERE pa.activitat NOT IN ('CTS', 'TRU', 'Test')
                  );

This assumes that each table does not have duplicates. You might need COUNT(DISTINCT).

Improve this answer

Comments

0

You could use the IN operator, since you want to compare each "activitat" to each result from your SQL query. Just make sure not to return multiple columns in the ($sql) query.

SELECT user 
FROM activitats_completades 
WHERE activitat IN
    ($sql)
AND user NOT IN 
    (SELECT user 
     FROM puntuacions_setmanals 
     WHERE setmana=1)
Improve this answer

3 Comments

Yes, but I need to get users that have done all the activities of the query, not one of them.
@msabate I would play with comparing the amount then: ... GROUP BY user HAVING COUNT(activitat) = *Amount of activities*. The amount of activities can be read with another sql statement.
It works for m, it's the same as the solution. Thanks!
0

Please use the IN operator for activitat column and using between for dia column :

SELECT activitat FROM planning_activitats WHERE activitat NOT IN ('CTS','TRU','Test') AND dia between 1 AND 7;

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.