Thank you very much for all the insightful replies. I think I can get it to work with a
join.
---- Joerg Bruehe <Joerg.Bruehe@stripped> 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.Bruehe@stripped
> Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz
> Amtsgericht Muenchen: HRB161028
>
>
