[MySQL] Self join/[disc] GROUP BY, wat moet erin?

[Lethalis]

Ik ben een forumpje aan het programmeren en zit met een probleempje met betrekking tot de weergave van het overzicht.

Ik heb 1 tabel met berichten:

- id (integer)
- parent (integer, verwijst naar een ander id bij reactie, of is gelijk aan eigen id)
- auteur (integer, verwijst naar andere tabel)
- aangemaakt (datetime)
- gewijzigd (datetime)
- titel (varchar)
- inhoud (text)

Deze tabel bevat dus zowel oorspronkelijke berichten als reacties daarop. Wat ik wil is een overzicht van titel, auteur en het tijdstip van laatste reactie.

Zelf heb ik al een self-join geprobeerd, maar ik kom er niet helemaal uit:

select z.titel, z.auteur, r.aangemaakt from berichten z, berichten r where r.parent = z.id group by r.id order by r.aangemaakt desc

Mijn output momenteel:

code:

mysql> select z.titel, z.inhoud, z.auteur, r.aangemaakt
from berichten z, berichten r where r.parent = z.id
group by r.id order by r.aangemaakt desc;
+------------------+-----------------+--------+---------------------+
| titel     | inhoud        | auteur | aangemaakt       |
+------------------+-----------------+--------+---------------------+
| Andere topic     | Ja ja :P     | 1 | 2002-06-02 17:08:22 |
| Dit is een test! | Zei ik toch? :P |  1 | 2002-06-02 16:56:13 |
| Dit is een test! | Zei ik toch? :P |  1 | 2002-06-02 16:55:40 |
+------------------+-----------------+--------+---------------------+
3 rows in set (0.01 sec)

mysql>

Ik wil dus alleen de bovenste 2 regels hebben. Wat moet ik hiervoor veranderen aan mijn query? Ben zelf niet zo'n ster wat SQL betreft

Bij voorbaat dank.

[D2k]

DISTINCT

[whoami]

GROUP BY, MAX en even een SQL tutorial doorspitten.

[Lethalis]

Op zondag 02 juni 2002 18:26 schreef D2k het volgende:
DISTINCT

Als ik dat toevoeg, krijg ik dezelfde output.

[D2k]

welk verschil zit er dan tussen de bovenste 2 en die eronder?

[Lethalis]

Op zondag 02 juni 2002 18:29 schreef D2k het volgende:
welk verschil zit er dan tussen de bovenste 2 en die eronder?

Regel 1 en 3 geven de oorspronkelijke berichten weer. Ik wil dus het tijdstip van de laatste reactie hebben. Bij 1 gaat dit nog goed, omdat er geen reactie is. Bij 3 is er een reactie, namelijk 2. Het tijdstip is dus nieuwer, ik wil alleen dat hebben en niet de andere.

[Lethalis]

code:

mysql> select z.titel, z.inhoud, z.auteur, max(r.aangemaakt)
from berichten z, berichten r where r.parent = z.id
group by r.id order by r.aangemaakt desc;   +------------------+-----------------+--------+---------------------+
| titel     | inhoud        | auteur | max(r.aangemaakt)   |
+------------------+-----------------+--------+---------------------+
| Andere topic     | Ja ja :P     | 1 | 2002-06-02 17:08:22 |
| Dit is een test! | Zei ik toch? :P |  1 | 2002-06-02 16:56:13 |
| Dit is een test! | Zei ik toch? :P |  1 | 2002-06-02 16:55:40 |
+------------------+-----------------+--------+---------------------+
3 rows in set (0.01 sec)

mysql>

Dit is met MAX() .. wat doe ik fout?

[edit]
Ik heb hem

r.id moet r.parent zijn

Topic kan dicht thnx voor de tip

[whoami]

Je groepeert op de verkeerde velden imho.

[D2k]

geef je table dump met inhoud eens svp
(met deze 3 als inhoud svp)
dan zal ik ook wel ff proberen

.edit: een table dump dus

[Lethalis]

code:

3 rows in set (0.01 sec)

mysql> select z.titel, z.inhoud, z.auteur, max(r.aangemaakt)
from berichten z, berichten r where r.parent = z.id
group by r.parent order by r.aangemaakt desc;
+------------------+-----------------+--------+---------------------+
| titel     | inhoud        | auteur | max(r.aangemaakt)   |
+------------------+-----------------+--------+---------------------+
| Andere topic     | Ja ja :P     | 1 | 2002-06-02 17:08:22 |
| Dit is een test! | Zei ik toch? :P |  1 | 2002-06-02 16:56:13 |
+------------------+-----------------+--------+---------------------+
2 rows in set (0.02 sec)

mysql>

Nu doet 'ie het

[whoami]

Raar...
Ik dacht dat je altijd moest groupen op de velden in uw select-clause die niet tot een aggregate functie behoren? (In dit geval dus, z.titel, z.inhoud en z.auteur).
En jij groepeert op r.Parent? waarom groupeer je daarop? snappem nie.

[Lethalis]

r.parent komt in alle records voor, en is gelijk per bericht

[Lethalis]

code:

mysql> select z.titel, z.inhoud, z.auteur, count(r.id)
- 1 as replies, max(r.aangemaakt) from berichten z,
berichten r where r.parent = z.id group by r.parent
order by r.aangemaakt desc;
+------------------+-----------------+--------+---------+---------------------+
| titel     | inhoud        | auteur | replies | max(r.aangemaakt)   |
+------------------+-----------------+--------+---------+---------------------+
| Andere topic     | Ja ja :P     | 1 |  0 | 2002-06-02 17:08:22 |
| Dit is een test! | Zei ik toch? :P |  1 |  1 | 2002-06-02 16:56:13 |
+------------------+-----------------+--------+---------+---------------------+
2 rows in set (0.01 sec)

mysql>

Zo ziet het er nu uit. Met r.parent erbij zou het zo zijn:

code:

mysql> select z.titel, z.inhoud, z.auteur, count(r.id) - 1
as replies, max(r.aangemaakt), r.parent from berichten z,
berichten r where r.parent = z.id group by r.parent
order by r.aangemaakt desc;
+------------------+-----------------+--------+---------+---------------------+--------+
| titel     | inhoud        | auteur | replies | max(r.aangemaakt)   | parent |
+------------------+-----------------+--------+---------+---------------------+--------+
| Andere topic     | Ja ja :P     | 1 |  0 | 2002-06-02 17:08:22 |  3 |
| Dit is een test! | Zei ik toch? :P |  1 |  1 | 2002-06-02 16:56:13 |  1 |
+------------------+-----------------+--------+---------+---------------------+--------+
2 rows in set (0.01 sec)

mysql>

Zie je?

[D2k]

geef toch die table dump nog maar ff
der zijn nog wel wat mensen die met je gegevens aan de slag willen

[Lethalis]

De hele tabel:

code:

mysql> select * from berichten;
+----+--------+--------+---------------------+---------------------+-----------------------+----------------------------+
| id | parent | auteur | aangemaakt     | gewijzigd      | titel             | inhoud              |
+----+--------+--------+---------------------+---------------------+-----------------------+----------------------------+
|  1 |  1 | 1 | 2002-06-02 16:55:40 | 2002-06-02 16:55:40 | Dit is een test!    | Zei ik toch? :P       |
|  2 |  1 | 1 | 2002-06-02 16:56:13 | 2002-06-02 16:56:13 | Re: Dit is een test!! | En dit is de reactie erop! |
|  3 |  3 | 1 | 2002-06-02 17:08:22 | 2002-06-02 17:08:22 | Andere topic        | Ja ja :P           |
+----+--------+--------+---------------------+---------------------+-----------------------+----------------------------+
3 rows in set (0.00 sec)

mysql>

Have fun .. ofzo

[Lethalis]

Ik heb momenteel onderstaande query in mijn PHP script staan

code:

mysql> select z.titel, z.inhoud, z.auteur,
count(r.id) - 1 as replies, max(r.aangemaakt) as lastreply,
r.parent from berichten z, berichten r where r.parent = z.id
group by r.parent order by r.aangemaakt desc;
+------------------+-----------------+--------+---------+---------------------+--------+
| titel     | inhoud        | auteur | replies | lastreply       | parent |
+------------------+-----------------+--------+---------+---------------------+--------+
| Andere topic     | Ja ja :P     | 1 |  0 | 2002-06-02 17:08:22 |  3 |
| Dit is een test! | Zei ik toch? :P |  1 |  1 | 2002-06-02 16:56:13 |  1 |
+------------------+-----------------+--------+---------+---------------------+--------+
2 rows in set (0.01 sec)

mysql>

Ik weet niet of het beter kan .. maar het schijnt te werken

[whoami]

Op zondag 02 juni 2002 18:42 schreef Lethalis het volgende:
r.parent komt in alle records voor, en is gelijk per bericht

Ja, maar je selecteert dat veld niet. Daarmee zou uw RDBMS in principe (imho) dat veld niet kunnen kennen, want het staat niet in de select list. (Net zoals je enkel kunt sorteren op velden die in de SELECT-list staan).

Ik weet niet of het beter kan .. maar het schijnt te
werken

De naamgeving kan alleszins beter according to me. r en z enzo..

[D2k]

Op zondag 02 juni 2002 19:01 schreef whoami het volgende:

[..]

Ja, maar je selecteert dat veld niet. Daarmee zou uw RDBMS in principe (imho) dat veld niet kunnen kennen, want het staat niet in de select list. (Net zoals je enkel kunt sorteren op velden die in de SELECT-list staan).

mysql vreet het allemaal
als je zoiets in een echte RDBMS zou proberen zou het niet werken volgens mij

ik ga het zo wel ff in interbase testen

[Lethalis]

Naamgeving en selectie aangepast:

code:

mysql> select zelf.titel, zelf.inhoud, zelf.auteur,
count(reply.id) - 1 as replies, max(reply.aangemaakt)
as lastreply, reply.parent, zelf.id from berichten zelf,
berichten reply where reply.parent = zelf.id group by
reply.parent order by reply.aangemaakt desc;
+------------------+-----------------+--------+---------+---------------------+--------+----+
| titel     | inhoud        | auteur | replies | lastreply       | parent | id |
+------------------+-----------------+--------+---------+---------------------+--------+----+
| Andere topic     | Ja ja :P     | 1 |  0 | 2002-06-02 17:08:22 |  3 |  3 |
| Dit is een test! | Zei ik toch? :P |  1 |  1 | 2002-06-02 16:56:13 |  1 |  1 |
+------------------+-----------------+--------+---------+---------------------+--------+----+
2 rows in set (0.01 sec)

mysql>

Zou hij nu werken in een 'echt' DBMS?

[whoami]

Op zondag 02 juni 2002 19:08 schreef Lethalis het volgende:
Zou hij nu werken in een 'echt' DBMS?

De GROUP BY clause is nog niet aangepast zie ik... ff wachten op de testresults van D2k.

[Lethalis]

Nog een keer veranderd:

code:

mysql> select zelf.id, zelf.titel, zelf.auteur,
count(reply.id) - 1 as replies, max(reply.aangemaakt) as lastreply, reply.parent
from berichten zelf, berichten reply
where reply.parent = zelf.id
group by reply.parent
order by lastreply desc;
+----+------------------+--------+---------+---------------------+--------+
| id | titel        | auteur | replies | lastreply       | parent |
+----+------------------+--------+---------+---------------------+--------+
|  3 | Andere topic     |   1 |  0 | 2002-06-02 17:08:22 |  3 |
|  1 | Dit is een test! |   1 |  1 | 2002-06-02 16:56:13 |  1 |
+----+------------------+--------+---------+---------------------+--------+
2 rows in set (0.01 sec)

mysql>

Wat moet er anders in de GROUP BY dan?

[D2k]

Op zondag 02 juni 2002 18:43 schreef Lethalis het volgende:

code:

mysql> select z.titel, z.inhoud, z.auteur, count(r.id)
- 1 as replies, max(r.aangemaakt) from berichten z,
berichten r where r.parent = z.id group by r.parent
order by r.aangemaakt desc;

gaat fout op de group by

Dynamic SQL Error
SQL error code = -104
invalid column reference
Statement: select
z.titel,
z.inhoud,
z.auteur,
count(r.id)- 1 as replies,
max(r.aangemaakt)
from
berichten z,
berichten r
where
r.parent = z.id
group by
r.parent
order by
r.aangemaakt desc

Zo ziet het er nu uit. Met r.parent erbij zou het zo zijn:

code:

mysql> select z.titel, z.inhoud, z.auteur, count(r.id) - 1
as replies, max(r.aangemaakt), r.parent from berichten z,
berichten r where r.parent = z.id group by r.parent
order by r.aangemaakt desc;

Zie je?

gaat fout op de group by

Dynamic SQL Error
SQL error code = -104
invalid column reference
Statement: select z.titel, z.inhoud, z.auteur, count(r.id) - 1
as replies, max(r.aangemaakt), r.parent from berichten z,
berichten r where r.parent = z.id group by r.parent
order by r.aangemaakt desc

Op zondag 02 juni 2002 19:08 schreef Lethalis het volgende:
Naamgeving en selectie aangepast:

code:

mysql> select zelf.titel, zelf.inhoud, zelf.auteur,
count(reply.id) - 1 as replies, max(reply.aangemaakt)
as lastreply, reply.parent, zelf.id from berichten zelf,
berichten reply where reply.parent = zelf.id group by
reply.parent order by reply.aangemaakt desc;

Zou hij nu werken in een 'echt' DBMS?

gaat fout op de group by

Dynamic SQL Error
SQL error code = -104
invalid column reference
Statement: select zelf.titel, zelf.inhoud, zelf.auteur,
count(reply.id) - 1 as replies, max(reply.aangemaakt)
as lastreply, reply.parent, zelf.id from berichten zelf,
berichten reply where reply.parent = zelf.id group by
reply.parent order by reply.aangemaakt desc

nu nog ff een goede maken

[D2k]

Op zondag 02 juni 2002 19:30 schreef Lethalis het volgende:
Nog een keer veranderd:

zie de vorige 3x:P

[whoami]

Op zondag 02 juni 2002 19:30 schreef Lethalis het volgende:

Wat moet er anders in de GROUP BY dan?

code:

GROUP BY zelf.id, zelf.titel, zelf.auteur

Alles wat in uw select-list staat dus (in dit geval) exclusief de velden die bekomen werden door aggregate function (COUNT, MAX, ...)

[D2k]

code:

select 
    zelf.id, 
    zelf.titel, 
    zelf.auteur,
    count(reply.id) - 1 as replies, 
    max(reply.aangemaakt) as lastreply, 
    reply.parent
from 
    berichten zelf, 
    berichten reply
where 
    reply.parent = zelf.id
group by 
    zelf.id, 
    zelf.titel, 
    zelf.auteur,
    reply.parent
order by 
    reply.aangemaakt desc;

deze werkt in interbase

[ACM]

D2k: dat je een melding op de group by kreeg had ik je ook wel kunnen vertellen

[D2k]

Op zondag 02 juni 2002 19:35 schreef ACM het volgende:
D2k: dat je een melding op de group by kreeg had ik je ook wel kunnen vertellen

jaja
werkt die interbase query in postgres? kan jij dat ff testen svp

[ACM]

Op zondag 02 juni 2002 19:36 schreef D2k het volgende:
werkt die interbase query in postgres? kan jij dat ff testen svp

Ik ben lui en ga het dus niet testen, maar ik gok erop dat ie het wel doet

[D2k]

Op zondag 02 juni 2002 19:37 schreef ACM het volgende:

[..]

Ik ben lui en ga het dus niet testen, maar ik gok erop dat ie het wel doet

wimp

[Lethalis]

Hmm, thanx Ik neem jouw query over.

Ik wist niet dat al die velden in de GROUP BY horen .. GROUP BY en ORDER BY zijn toch van toepassing op de MAX en COUNT functies? Zou het logischerwijs dan niet genoeg zijn om alleen zelf.id en reply.parent in de GROUP BY te zetten?

[whoami]

Neen. GROUP BY is van toepassing op de velden die in uw select list staan. Aangezien uw id er niet in stond, is het logisch dat hij daar een fout op gaf.

[Lethalis]

Op zondag 02 juni 2002 20:31 schreef whoami het volgende:
Neen. GROUP BY is van toepassing op de velden die in uw select list staan. Aangezien uw id er niet in stond, is het logisch dat hij daar een fout op gaf.

OK

[marty]

D2k schreef op 02 juni 2002 @ 19:35:

code:

select 
    zelf.id, 
    zelf.titel, 
    zelf.auteur,
    count(reply.id) - 1 as replies, 
    max(reply.aangemaakt) as lastreply, 
    reply.parent
from 
    berichten zelf, 
    berichten reply
where 
    reply.parent = zelf.id
group by 
    zelf.id, 
    zelf.titel, 
    zelf.auteur,
    reply.parent
order by 
    reply.aangemaakt desc;

deze werkt in interbase

Op zoek naar een soortgelijke oplossing kwam ik dit tegen (erg fijn trouwens, want m'n probleem is opgelost!), ik zag alleen nog een foutje staan (denk ik)
je ORDER BY moet volgens mij
ORDER BY lastreply DESC
zijn.

Het boeit jullie waarschijnlijk niet meer....maar wie weet voor de volgende die op zoek is