List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:June 16 2010 12:44pm
Subject:RE: query help
View as plain text  
i would monitor the performance on outer-join to determine if your server pegging cpu,disk
i/o or memory when executing te outer-join

then perhaps populating a temp table (and deleting the non-matching records..those records
which will be considered in transaction) as joerg suggested

 

i was hoping to use a trigger
perhaps a trigger may work is it possible to initiate the trigger on login/connect or some
other initiating event when entering the database.. thus far trigger events are DML only?

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

 

if not then you can create a test script which will create and populate the temp table
thru cron

http://www.databasejournal.com/features/mysql/article.php/3833146/Running-MySQL-in-Batch-Mode.htm

 

would be interested to know which solution works best 

 

Vielen Danke,
Martin 
______________________________________________ 
Verzicht und Vertraulichkeitanmerkung

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so
bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer
Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und
entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

 

> From: richard@stripped
> To: Joerg.Bruehe@stripped; mysql@stripped
> Subject: Re: query help
> CC: jblanchard@stripped; richard@stripped
> Date: Wed, 16 Jun 2010 08:23:21 -0400
> 
> 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
> > 
> > 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> 
 		 	   		  
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. 
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5
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