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: rich...@rushlogistics.com > To: joerg.bru...@sun.com; mysql@lists.mysql.com > Subject: Re: query help > CC: jblanch...@pocket.com; rich...@rushlogistics.com > 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.bru...@sun.com> 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.bru...@sun.com > > 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=mgai...@hotmail.com > _________________________________________________________________ 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
