Hi!
Jay Blanchard wrote: > [snip] > I have a table similar to this: > > ------------------------- > |transactions | > |ID |DATE |EMPLOYEE| > |234 |2010-01-05| 345 | > |328 |2010-04-05| 344 | > |239 |2010-01-10| 344 | > > Is there a way to query such a table to give the days of the year that > employee 344 did not have a transaction? > [/snip] > > SELECT DATE > FROM transactions > WHERE EMPLOYEE != '344' > GROUP BY DATE; I strongly doubt this will work - what if several employees have transactions on the same day? No, what the poster effectively needs is a set difference: Take the set of all candidate dates, and subtract the set of days on which the employee in question did have a transaction. The first difficulty will be to construct the set of candidate dates, as this needs a decision what to do about non-working dates (weekends, public holidays, ...) and how to determine them - depending on the business logic, that set may be specific to the employee (personal vacation!). Only when this has been decided, there is the question how to implement the set difference: - SQL "minus" is a candidate, but MySQL doesn't support that AFAIK. - Outer Join is the other possibility, as proposed by Gavin. - Having all candidate dates in some temporary table and then deleting those with a transaction is another way, but probably very slow. (The advantage of this might be that it is the most flexible way.) Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
