[SQL] Extra rows na inner join

[Verwijderd]

Ik ben druk bezig met het schrijven van een leuk stukje SQL:

code:

select 

t.title,
s.user_id as starter, s.init_date as first_date,
e.user_id as last_poster, e.init_date as last_date

from thread t

inner join message s on t.thread_id = s.thread_id and s.reply_id = 1 
inner join message e on t.thread_id = e.thread_id 

group by t.thread_id, t.forum_id, t.title, t.interaction, t.type,
s.reply_id, s.user_id, s.init_date,
e.reply_id, e.user_id, e.init_date

order by last_date desc, e.reply_id

Het doel van deze sql is een resultaat dat te vergelijken is met de active topics op GoT. In de bovenstaande query heb ik overigens de onbelangrijke informatie weggelaten.

Het resltaat van bovenstaande query staat hieronder beschreven:

code:

t.title                     starter first_date              last poster last_date
Ervaringen volgelaats masker    32  2003-01-02 00:37:09.453 3   2003-01-05 10:28:27.107
Ervaringen volgelaats masker    32  2003-01-02 00:37:09.453 32  2003-01-04 22:40:18.893
Ervaringen volgelaats masker    32  2003-01-02 00:37:09.453 32  2003-01-04 22:34:44.153
Het gaat niet vanzelf hoor  3   2003-01-04 11:06:54.490 3   2003-01-04 11:06:54.490
Registreer knop.............    2   2003-01-02 23:59:48.893 3   2003-01-03 00:08:33.057
Registreer knop.............    2   2003-01-02 23:59:48.893 2   2003-01-02 23:59:48.893
Ervaringen volgelaats masker    32  2003-01-02 00:37:09.453 2   2003-01-02 22:44:59.667
Advanced open water boek    33  2003-01-02 12:08:07.567 2   2003-01-02 22:43:28.030
Forum namen...............  4   2003-01-02 02:42:58.557 3   2003-01-02 14:16:27.490
Forum namen................ 4   2003-01-02 02:42:58.557 4   2003-01-02 14:06:14.213

Nou ben ik zoals je ziet al een heel eind. Het enige wat nu nog dwars zit is het meerdere malen voorkomen van zowel titel als starter als first_date.

Dit is toe te schrijven aan de tweede inner join die verantwoordelijk is voor de laatste twee kolommen (last poster en last_date). Wat nu de bedoeling is:

Ik wil alleen het bovenste record van een rijtje terug zien (de bij elkaar horende records onderscheiden zich overigens door de kolom reply_id, daar wordt ook op gesorteerd zoals je ziet).

Ik kan me voorstellen dat de tweede inner join er ongeveer als volgt uit zou kunnen zien (onderstaande werkt uiteraard niet)

code:

inner join message e on t.thread_id = e.thread_id and e.reply_id = max(e.reply_id)

Het gewenste resultaat:

code:

t.title                     starter first_date              last poster last_date
Ervaringen volgelaats masker    32  2003-01-02 00:37:09.453 3   2003-01-05 10:28:27.107
Het gaat niet vanzelf hoor  3   2003-01-04 11:06:54.490 3   2003-01-04 11:06:54.490
Registreer knop.............    2   2003-01-02 23:59:48.893 3   2003-01-03 00:08:33.057
Advanced open water boek    33  2003-01-02 12:08:07.567 2   2003-01-02 22:43:28.030
Forum namen...............  4   2003-01-02 02:42:58.557 3   2003-01-02 14:16:27.490

komt u maar

[ Voor 5% gewijzigd door Verwijderd op 05-01-2003 15:33 ]

[D2k]

DISTINCT
of is dat te simpel?

[ACM]

group by en max(date) denk ik eerder
Nadeel is dat je dan niet perse weet wie de last-post deed, in react gebeurt dat iets anders, die waarden worden in de topictabel gecached en de hele message-tabel komt er niet aan te pas.

[Verwijderd]

maarre, hoe heb je "group by en max_date" dan in gedachte?

[Verwijderd]

code:

SELECT blabla, blabla, max_date(last_date) FROM tabellen GROUP BY topic

Zoiets misschien?

[ Voor 1% gewijzigd door Verwijderd op 05-01-2003 15:47 . Reden: slash vergeten |:( ]

[ACM]

Verwijderd schreef op 05 January 2003 @ 15:44:
Zoiets misschien?

Idd

[ Voor 27% gewijzigd door ACM op 05-01-2003 15:45 ]

[Verwijderd]

dat zou idd de oplossing zijn.... ware het niet dat alle velden die in de select staan, ook in de group by moeten staan

[Verwijderd]

het is gelukt met:

code:

select 

t.title,
s.user_id as starter, s.init_date as first_date,
e.user_id as last_poster, e.init_date as last_date

from thread t

inner join message s on t.thread_id = s.thread_id and s.reply_id = 1 
inner join message e on t.thread_id = e.thread_id = (select top 1 reply_id from message where thread_id = s.thread_id order by reply_id desc)

group by t.thread_id, t.forum_id, t.title, t.interaction, t.type,
s.reply_id, s.user_id, s.init_date,
e.reply_id, e.user_id, e.init_date

order by last_date desc, e.reply_id

[ACM]

Verwijderd schreef op 05 January 2003 @ 15:52:
dat zou idd de oplossing zijn.... ware het niet dat alle velden die in de select staan, ook in de group by moeten staan

Bij mysql niet, mocht je dat gebruiken, maar wat is het probleem dat die velden erin moeten staan? Dat je dus alsnog twee losse krijgt zeker?

In dat geval (als je toch geen mysql gebruikt) kan je natuurlijk dmv een subquery de laatste posts eruit halen en die gebruiken in de rest van de select.

ala:
select ... from ... where message.id = select (max(id) from messages ...)

of natuurlijk op datum

[edit]
Ah, was je al achter

[ Voor 3% gewijzigd door ACM op 05-01-2003 16:14 ]

[Verwijderd]

dank

[klinz]

D2k schreef op 05 januari 2003 @ 15:32:
DISTINCT
of is dat te simpel?

Onze DBA zegt altijd: DISTINCT is voor mensen die geen SQL kunnen. Bij dubbele records zit er meestal iets fout in je JOIN. Een DISTINCT is dan meer een lapmiddel dan waar het werkelijk voor gebruikt dient te worden.

[ACM]

klinz schreef op 05 januari 2003 @ 19:55:
Onze DBA zegt altijd: DISTINCT is voor mensen die geen SQL kunnen. Bij dubbele records zit er meestal iets fout in je JOIN. Een DISTINCT is dan meer een lapmiddel dan waar het werkelijk voor gebruikt dient te worden.

select count(distinct(typeid)) from messages; -- hoeveel verschillende types zijn daadwerkelijk in gebruik.

Doe dat es zonder distinct?
In dit geval heb je natuurlijk wel gelijk, maar er is geen algemene regel op te trekken.

[ Voor 9% gewijzigd door ACM op 05-01-2003 20:40 ]