[sql] Left join probleem

[w00d]

Ik heb 2 tabellen met daarin de login en loguit tijden van gebruikers. Nu wil ik een overzicht van wanneer iemand is ingelogd en uiteraard ook weer uitgelogd.

de tabellen:
Logon:

Username	Time	Date
Ruud	10:14:47	4-2-2008
Ruud	10:40:46	4-2-2008

Logooff:

Username	Time	Date
Ruud	10:15:48	4-2-2008

Ik heb me dus 2x aangemeld en 1x afgemeld ( pc een keer geherstart )
nu gebruik ik de volgende sql code:

code:

SELECT logon.user, logon.time AS logon_time, logon.date AS logon_date, logoff.time AS logoff_time, logoff.date AS logoff_date 
FROM logon 
LEFT JOIN logoff ON logon.user = logoff.user 
WHERE logon.date = logoff.date 
AND logon.user = 'ruud'

Ik verwacht het volgende resultaat:

Username	logon_time	logon_date	logoff_time	logoff_date
Ruud	10:14:47	4-2-2008	10:15:48	4-2-2008
Ruud	10:40:46	4-2-2008

maar krijgt helaas dit:

Username	logon_time	logon_date	logoff_time	logoff_date
Ruud	10:14:47	4-2-2008	10:15:48	4-2-2008
Ruud	10:40:46	4-2-2008	10:15:48	4-2-2008

Het resultaat opzich is wel logisch, immer de gebruikers en datum moeten overeen komen. Maar hoe kan ik er nou voor zorgen dat een record uit 1 van de tabellen maar 1x gebruikt mag worden. DISTINCT werkt hier niet omdat de uitkomst ( rij opzich ) uniek is.

Iemand een idee wat ik hier fout doe?

[Confusion]

De logon tabel een synthetic primary key geven en de logoff tabel een referentie naar de regel geven die de bijbehorende logon bevat.

Op tijd bij elkaar gaan zoeken welke logoff bij welke logon hoort is te doen, maar onnodig complex.

[EfBe]

SELECT logon.user, logon.time AS logon_time, logon.date AS logon_date, logoff.time AS logoff_time, logoff.date AS logoff_date
FROM logon
LEFT JOIN logoff ON logon.user = logoff.user
AND logon.logon_date<= logoff_date AND logon.logon_time <= logoff.logoff_time
WHERE logon.date = logoff.date
AND logon.user = 'ruud'

[w00d]

Helaas, hierdoor wordt de laatste regel niet weergegeven.

hier nog een voorbeeld van het probleem. Zoals je ziet zit er 1 regel te veel in. Op 1-2-2008 is de gebruiker om 6.25.74 aangemeld, en om 12:53 afgemeld. daarna weer om 15:19 aangemeld en om 16:07 afgemeld.

Ook dit gaat dus verkeerd.

username logontime logondate logofftime logoffdate
gebruiker 07:01:11 4-2-2008 16:10:34 4-2-2008
gebruiker 15:19:26 1-2-2008 16:07:21 1-2-2008
gebruiker 06:52:47 1-2-2008 16:07:21 1-2-2008
gebruiker 06:52:47 1-2-2008 12:53:28 1-2-2008


@Confusion: dat gaat niet werken om dat er niet altijd een logoff regel is ( denk ik, kon niet meer informatie vinden over hoe een synthetic primary key werkt)

[Woy]

Ik zou idd je logon tabel een key geven ( session key ) en bij logof refereren naar diezelfde key. Zo werkt het ook als je op meerdere computers met dezelfde username inlogt ( zoals bij tweakers ook kan ).

Deze key kan je dan in je session of login cookie stoppen ( ligt eraan hoe je login systeem werkt ) en gebruik je bij het uitloggen.

edit:
het haalt niet uit dat er niet altijd een logof is. je kan toch gewoon een left join doen.

De volgende tabellen

code:

Login( ID ( autogenerated ), username, dateTime )
Logoff( ID, LoginID, username, dateTime )

en dan select

code:

select login.username, login.dateTime, logoff.dateTimte
from login
left join login.ID = logoff.LoginID

[ Voor 35% gewijzigd door Woy op 08-02-2008 16:04 ]

[Confusion]

EfBe schreef op vrijdag 08 februari 2008 @ 15:46:
SELECT logon.user, logon.time AS logon_time, logon.date AS logon_date, logoff.time AS logoff_time, logoff.date AS logoff_date
FROM logon
LEFT JOIN logoff ON logon.user = logoff.user
AND logon.logon_date<= logoff_date AND logon.logon_time <= logoff.logoff_time
WHERE logon.date = logoff.date
AND logon.user = 'ruud'

Die join levert per logoff meerdere logons op, aangezien er diverse logon_times voor een bepaalde logoff_time kunnen zijn.

w00d schreef op vrijdag 08 februari 2008 @ 15:59:
@Confusion: dat gaat niet werken om dat er niet altijd een logoff regel is ( denk ik, kon niet meer informatie vinden over hoe een synthetic primary key werkt)

Dat is gewoon een int auto_increment kolom. Ze noemen dat 'synethtic', omdat het enkel als rowId dient en geen business informatie bevat.