Cant get cool query

[aherraiz]

Hi,

I've worked with sql for some time now but i can't figure out this one. I'm pretty sure i've come across a very similar query before and i know there is a neat, good solution to this one, hope you can give me a hand.

The schema (simplified) is as follows:

TABLE staff
============
id | name
============
1 | john
2 | mark
3 | brown
============

TABLE staff_worked
=============== =============== =============== ========
staffid (FK staff.id) | days | week_ending | worked_on
=============== =============== =============== ========
1 | 3 | 2002-01-11 | A
1 | 2 | 2002-01-11 | B
2 | 5 | 2002-01-11 | A
3 | 2 | 2002-01-11 | C
1 | 1.5 | 2002-01-18 | D
=============== =============== =============== ========

staff_worked has a column 'id' as PK but i don't think is of any use in here. This table records the number of days per week a staff has worked on something. This something is irrelevant for this query but i've included it to make more sense of the table.

That's it, only two tables. Now, what I want is to know what members of staff have entered less than 5 days in a weekly basis or have not entered anything at all (if no one has entered anything for a certain week then we don't care). We assume that the maximum number of days a staff can enter is 5. We also assume ALL staff must enter something every week.

So, the kind of output i'm looking for according to the schema above is:

QUERY result
=============== ===============
staffid | days | week_ending
=============== ===============
3 | 2 | 2002-01-11
1 | 1.5 | 2002-01-18
2 | 0 | 2002-01-18
3 | 0 | 2002-01-18
=============== ===============

I hope it makes sense. Note that staff ids 1 & 2 don't come up for the week ending 2002-01-11 because they've entered 5 days and staff ids 2 and 3 are shown in week_ending 2002-01-18 because they haven't entered anything and there's one entry for that week (staff id 1)


Any help would be much appreciated.

Happy coding.

[ilearneditonline]

Not sure if this is what you are looking for or not.

SELECT a.id, a.name, b.days, b.week_ending
FROM staff a LEFT OUTER JOIN staff_worked b
ON a.id=b.staffid
WHERE b.days < 5

[aherraiz]

Not sure if this is what you are looking for or not.

SELECT a.id, a.name, b.days, b.week_ending
FROM staff a LEFT OUTER JOIN staff_worked b
ON a.id=b.staffid
WHERE b.days < 5

Thanks for your reply. However that won't give me what I need. I concluded that I need to dome some coding with the query result to accomplish the final result so I'm afraid MySQL is not enough in this case.

[henryrhenryr]

Hi

I was having a go but I wasn't quite getting it. Like you say, it will probably be easier to play with the results rather than try to get the outcome purely in SQL. Perhaps you could do it if you changed your table structure. Like moving the 'worked_on' column to another table and having total_days_work ed column in the staff_worked table. Then you would be able to use an IF condition in the query.

Sorry - not very helpful!

Henry

[pbmods]

Heya, Aherraiz.

This is as close as I can get you:

SELECT
        `id`,
        SUM(`days`)  -- Some staff have multiple logs per week.
            AS `days`,
        `week_ending`
    FROM
    (
            `staff`
        LEFT JOIN
            `staff_worked`
                ON
                    `staff`.`id` = `staff_worked`.`staffid`
    )
    WHERE
    (
        `week_ending`  -- Only show weeks where somebody worked.
            IN
            (
                SELECT
                    DISTINCT
                        `week_ending`
                    FROM
                        `staff_worked`
                    WHERE
                        `days` > 0
                    GROUP BY
                        `week_ending`
                    ORDER BY  -- IN subclause s/b sorted.
                        `week_ending` ASC
            )
    )
    GROUP BY
        `week_ending`,
        `staffid`
    HAVING
        `days` < 5  -- 51aX0rZ!
    ORDER BY
        `week_ending` ASC,
        `staffid` ASC
 

The problem with showing staff who logged zero days has to do with a fundamental limitation of MySQL: It can't fetch what isn't there.