List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:June 16 2010 12:01pm
Subject:Re: query help
View as plain text  

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]
> FROM transactions

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

query helpRichard Reina15 Jun
  • RE: query helpGavin Towey15 Jun
  • RE: query helpJay Blanchard15 Jun
    • Re: query helpJoerg Bruehe16 Jun
  • Re: query helpRichard Reina16 Jun
    • RE: query helpMartin Gainty16 Jun