Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '13 at 20:54
add comment

1 Answer

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 ; 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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