From: Martin Gainty
Date: June 16 2010 12:44pm
Subject: RE: query help
List-Archive: http://lists.mysql.com/mysql/221927
Message-Id: <BLU142-W33644A91EFCB3BEDC33921AEDE0@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_e83ed3e6-5d39-4321-b163-fb9a7a2b0bb1_"

--_e83ed3e6-5d39-4321-b163-fb9a7a2b0bb1_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


i would monitor the performance on outer-join to determine if your server p=
egging cpu=2Cdisk 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

=20

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 fa=
r trigger events are DML only?

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

=20

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-M=
ySQL-in-Batch-Mode.htm

=20

would be interested to know which solution works best=20

=20

Vielen Danke=2C
Martin=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.

=20

> From: richard@stripped
> To: Joerg.Bruehe@stripped=3B mysql@stripped
> Subject: Re: query help
> CC: jblanchard@stripped=3B richard@stripped
> Date: Wed=2C 16 Jun 2010 08:23:21 -0400
>=20
> Thank you very much for all the insightful replies. I think I can get it =
to work with a join.=20
>=20
>=20
> ---- Joerg Bruehe <Joerg.Bruehe@stripped> wrote:
> >
> > Hi!
> >=20
> >=20
> > Jay Blanchard wrote:
> > > [snip]
> > > I have a table similar to this:
> > >=20
> > > -------------------------
> > > |transactions |
> > > |ID |DATE |EMPLOYEE|
> > > |234 |2010-01-05| 345 |
> > > |328 |2010-04-05| 344 |
> > > |239 |2010-01-10| 344 |
> > >=20
> > > Is there a way to query such a table to give the days of the year tha=
t
> > > employee 344 did not have a transaction?
> > > [/snip]
> > >=20
> > > SELECT DATE
> > > FROM transactions
> > > WHERE EMPLOYEE !=3D '344'
> > > GROUP BY DATE=3B
> >=20
> > I strongly doubt this will work - what if several employees have
> > transactions on the same day?
> >=20
> > No=2C what the poster effectively needs is a set difference:
> > Take the set of all candidate dates=2C and subtract the set of days on
> > which the employee in question did have a transaction.
> >=20
> > The first difficulty will be to construct the set of candidate dates=2C=
 as
> > this needs a decision what to do about non-working dates (weekends=2C
> > public holidays=2C ...) and how to determine them - depending on the
> > business logic=2C that set may be specific to the employee (personal
> > vacation!).
> >=20
> > Only when this has been decided=2C there is the question how to impleme=
nt
> > the set difference:
> > - SQL "minus" is a candidate=2C but MySQL doesn't support that AFAIK.
> > - Outer Join is the other possibility=2C as proposed by Gavin.
> > - Having all candidate dates in some temporary table and then deleting
> > those with a transaction is another way=2C but probably very slow.
> > (The advantage of this might be that it is the most flexible way.)
> >=20
> >=20
> > J=F6rg
> >=20
> > --=20
> > Joerg Bruehe=2C MySQL Build Team=2C Joerg.Bruehe@stripped
> > Sun Microsystems GmbH=2C Komturstrasse 18a=2C D-12099 Berlin
> > Geschaeftsfuehrer: Juergen Kunz
> > Amtsgericht Muenchen: HRB161028
> >=20
> >=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped
>=20
 		 	   		 =20
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmulticalendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_e83ed3e6-5d39-4321-b163-fb9a7a2b0bb1_--