query help

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 empl=
oyee 344 did not have a transaction?

Thanks for the help.

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: query help

SGkgUmljaGFyZCwNCg0KVGhpcyBpcyBhIExFRlQgSk9JTiwgYnV0IHdpdGgg b25seSBvbmUgdGFi
bGUgeW91IG1heSBiZSB3b25kZXJpbmcgd2hhdCB0byBqb2luLiAgVHlwaWNh bGx5IHlvdSdsbCBo
YXZlIHRvIGNyZWF0ZSBhIG5ldyB0YWJsZSB0aGF0IGNvbnRhaW5zIGFsbCB0 aGUgZGF0ZXMgaW4g
dGhlIHJhbmdlIHlvdSB3YW50IHRvIGNoZWNrLiAgVGhlbiB5b3UgbGVmdCBq b2luIHlvdXIgdHJh
bnNhY3Rpb24gdGFibGUsIGFuZCBhbGwgdGhlIHJvd3MgZnJvbSB5b3VyIGRh dGVzIHRhYmxlIHRo
YXQgZG9uJ3QgaGF2ZSBhIG1hdGNoIGlzIHlvdXIgYW5zd2VyLg0KDQpSZWdh cmRzLA0KR2F2aW4g
VG93ZXkNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IFJp Y2hhcmQgUmVpbmEg
W21haWx0bzpyaWNoYXJkQHJ1c2hsb2dpc3RpY3MuY29tXQ0KU2VudDogVHVl c2RheSwgSnVuZSAx
NSwgMjAxMCAxMTozMCBBTQ0KVG86IG15c3FsQGxpc3RzLm15c3FsLmNvbQ0K U3ViamVjdDogcXVl
cnkgaGVscA0KDQoNCkkgaGF2ZSBhIHRhYmxlIHNpbWlsYXIgdG8gdGhpczoN Cg0KIC0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0NCnx0cmFuc2FjdGlvbnMgICAgICAgICAgICAg fA0KfElEICAgfERB
VEUgICAgICB8RU1QTE9ZRUV8DQp8MjM0ICB8MjAxMC0wMS0wNXwgMzQ1ICAg IHwNCnwzMjggIHwy
MDEwLTA0LTA1fCAzNDQgICAgfA0KfDIzOSAgfDIwMTAtMDEtMTB8IDM0NCAg ICB8DQoNCklzIHRo
ZXJlIGEgd2F5IHRvIHF1ZXJ5IHN1Y2ggYSB0YWJsZSB0byBnaXZlIHRoZSBk YXlzIG9mIHRoZSB5
ZWFyIHRoYXQgZW1wbG95ZWUgMzQ0IGRpZCBub3QgaGF2ZSBhIHRyYW5zYWN0 aW9uPw0KDQpUaGFu
a3MgZm9yIHRoZSBoZWxwLg0KDQpSaWNoYXJkDQoNCi0tDQpNeVNRTCBHZW5l cmFsIE1haWxpbmcg
TGlzdA0KRm9yIGxpc3QgYXJjaGl2ZXM6IGh0dHA6Ly9saXN0cy5teXNxbC5j b20vbXlzcWwNClRv
IHVuc3Vic2NyaWJlOiAgICBodHRwOi8vbGlzdHMubXlzcWwuY29tL215c3Fs P3Vuc3ViPWd0b3dl
eUBmZm4uY29tDQoNCg0KVGhpcyBtZXNzYWdlIGNvbnRhaW5zIGNvbmZpZGVu dGlhbCBpbmZvcm1h
dGlvbiBhbmQgaXMgaW50ZW5kZWQgb25seSBmb3IgdGhlIGluZGl2aWR1YWwg bmFtZWQuICBJZiB5
b3UgYXJlIG5vdCB0aGUgbmFtZWQgYWRkcmVzc2VlLCB5b3UgYXJlIG5vdGlm aWVkIHRoYXQgcmV2
aWV3aW5nLCBkaXNzZW1pbmF0aW5nLCBkaXNjbG9zaW5nLCBjb3B5aW5nIG9y IGRpc3RyaWJ1dGlu
ZyB0aGlzIGUtbWFpbCBpcyBzdHJpY3RseSBwcm9oaWJpdGVkLiAgUGxlYXNl IG5vdGlmeSB0aGUg
c2VuZGVyIGltbWVkaWF0ZWx5IGJ5IGUtbWFpbCBpZiB5b3UgaGF2ZSByZWNl aXZlZCB0aGlzIGUt
bWFpbCBieSBtaXN0YWtlIGFuZCBkZWxldGUgdGhpcyBlLW1haWwgZnJvbSB5 b3VyIHN5c3RlbS4g
RS1tYWlsIHRyYW5zbWlzc2lvbiBjYW5ub3QgYmUgZ3VhcmFudGVlZCB0byBi ZSBzZWN1cmUgb3Ig
ZXJyb3ItZnJlZSBhcyBpbmZvcm1hdGlvbiBjb3VsZCBiZSBpbnRlcmNlcHRl ZCwgY29ycnVwdGVk
LCBsb3N0LCBkZXN0cm95ZWQsIGFycml2ZSBsYXRlIG9yIGluY29tcGxldGUs IG9yIGNvbnRhaW4g
dmlydXNlcy4gVGhlIHNlbmRlciB0aGVyZWZvcmUgZG9lcyBub3QgYWNjZXB0 IGxpYWJpbGl0eSBm
b3IgYW55IGxvc3Mgb3IgZGFtYWdlIGNhdXNlZCBieSB2aXJ1c2VzIG9yIGVy cm9ycyBvciBvbWlz
c2lvbnMgaW4gdGhlIGNvbnRlbnRzIG9mIHRoaXMgbWVzc2FnZSwgd2hpY2gg YXJpc2UgYXMgYSBy
ZXN1bHQgb2YgZS1tYWlsIHRyYW5zbWlzc2lvbi4gW0ZyaWVuZEZpbmRlciBO ZXR3b3JrcywgSW5j
LiwgMjIwIEh1bWJvbHQgY291cnQsIFN1bm55dmFsZSwgQ0EgOTQwODksIFVT QSwgRnJpZW5kRmlu
ZGVyLmNvbQ0K

RE: query help

[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 !=3D '344'
GROUP BY DATE;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: query help

Hi!


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 that
> employee 344 did not have a transaction?
> [/snip]
>=20
> SELECT DATE
> FROM transactions
> WHERE EMPLOYEE !=3D '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

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: query help

Thank you very much for all the insightful replies. I think I can get it to=
work with a join. =20


---- Joerg Bruehe 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 that
> > employee 344 did not have a transaction?
> > [/snip]
> >=20
> > SELECT DATE
> > FROM transactions
> > WHERE EMPLOYEE !=3D '344'
> > GROUP BY DATE;
>=20
> I strongly doubt this will work - what if several employees have
> transactions on the same day?
>=20
> 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.
>=20
> 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!).
>=20
> 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.)
>=20
>=20
> Jörg
>=20
> --=20
> Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
> Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz
> Amtsgericht Muenchen: HRB161028
>=20
>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: query help

--_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/38 33146/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@rushlogistics.com
> To: Joerg.Bruehe@Sun.COM=3B mysql@lists.mysql.com
> Subject: Re: query help
> CC: jblanchard@pocket.com=3B richard@rushlogistics.com
> 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 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örg
> >=20
> > --=20
> > Joerg Bruehe=2C MySQL Build Team=2C Joerg.Bruehe@Sun.COM
> > 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@hotmail.com
>=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=3Dmultic alendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

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