Jay Blanchard wrote:
> 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?
> 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
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.)
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@stripped
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028