List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:June 16 2010 12:23pm
Subject:Re: query help
View as plain text  
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
> 
> 
Thread
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