Thank you very much for all the insightful replies. I think I can get it to work with a join.
---- Joerg Bruehe <joerg.bru...@sun.com> wrote: > > 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
