0

In my database I have 5 fix users, lets say user1, ..., user5.

Furthermore I have a table

month(name, finished, month, year)

where name is VARCHAR (the name of one of the five users) and the others are INTEGER. In row finished, 0 means not finished and 1 means finished (Note that finished does not mean 'the month is over' in this context). Every user has an entry for each month. I'm struggling to find a query which returns me the months where every user has the entry finished = 1.

SELECT month FROM month

obviously returns months, where other users can have finished=0

SELECT month FROM month WHERE finished=1

returns the finished months (multiple times), while other users can have finished=0 in this same month and I would have to check in the code if there are months appearing five times (which would mean that month is finished by all users).

Now, without creating triggers or similiar constructs, is there a way to check the desired requirement directly in the SQL-query?

Addendum: I know that a table monthAll(month, year, finishedAll) would be a solution, but setting it to finishedAll=1 if all five users have finished a certain month would equally require additional code in my JavaScript due to the fact how the whole program is constructed.

1
  • 2
    Side note: I'd suggest using different naming convention(at least for tables). I'd never figure out that month.name actually stores username...
    – a1ex07
    Jan 18, 2013 at 20:54

1 Answer 1

1

If there are 5 rows per month (1 for every user), this will do:

SELECT m.year, m.month 
FROM month AS m
GROUP BY m.year, m.month
HAVING SUM(finished) = 5 ; 

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.