[SQL] Query (info uit 4 tabellen) lukt niet

[Reveller]

Ik heb de volgende tabellen in mijn database:

code:

comments    |  stories      |  categories     |  users
--------    |  -------      |  ----------     |  -----
cid         |  sid          |  cid            |  uid
sid         |  category     |  name           |  name
uid         |  uid          |  url            |
name        |  title        |                 |
title       |  body         |                 |
body        |  created      |                 |
created     |  type         |                 |

Nu wilde ik graag een historisch overzicht maken van alle "stories" die in de database staan, met de gebruikers die ze gemaakt hebben. Na een hoop gepruts lukte dat met deze query:

SQL:

SELECT s.*, u.name, c.url 
FROM stories s, users u, categories c 
WHERE s.uid = u.uid 
  AND s.category = c.cid
ORDER BY s.created DESC

Het resulaat van deze query staat in de eerste twee records hieronder. Ik zou nu echter ook graag de comments in het resultaat terug zien (zoals in record 3), eventueel met als type en category, automatisch 'comment' ingevuld:

code:

sid | type    | category | title | body      | created    | uid | name  | url
--------------------------------------------------------------------------------------
90  | blog    | 10       | blaat | ik heb... | 1147628154 | 2   | admin | nieuws/2005
84  | page    | 8        | titel | sinds 5.. | 1147519549 | 5   | erik  | bedrijf/over
71  | comment |          | geen  | ik vind.. | 1146629211 |     | miep  |

In de tabel "comments" staat (zoals verwacht de reacties van mensen op de diverse content-items ("stories"). Anoniem comments invullen is mogelijk, vandaar dat "uid" leeg kan zijn. Het resultaat moet nog steeds op "created" gesorteerd worden. Ik probeerde onderstaande query, maar daarmee krijg ik elke record -tig keer terug:

SQL:

SELECT s.*, u.name, c.url, com.* 
FROM stories s, users u, categories c, comments com 
WHERE s.uid = u.uid 
  AND s.category = c.cid
ORDER BY s.created DESC

Wie kan mij met deze query helpen?

[NMe]

Je bent de joinconditie voor je nieuwe tabel vergeten. En aangezien er niet per se een comment hoeft te zijn, moet je een left join gebruiken.

[Verwijderd]

Je maakt geen referentie van je comments tabel naar je stories tabel, daarnaast moet je geen inner join maar een left join gebruiken wanneer het voor kan komen dat UID null is.

^^was sneller

[ Voor 6% gewijzigd door Verwijderd op 05-06-2006 16:27 ]

[Reveller]

Bedankt. Ik heb nu de volgende query, en hij werkt gedeeltelijk:

SQL:

SELECT s.* , u.name, c.url, com.*
FROM stories s, users u, categories c
LEFT JOIN comments com ON(s.sid = com.sid)
WHERE s.uid = u.uid AND s.category = c.cid
ORDER BY s.created DESC 

Zo heb ik in de tabel stories een story staan met sid (staat voor story-id) 82. In de comments tabel staan 4 comments met sid=82. Als ik deze query run, staat in het resultaat 4 keer de story met sid=82. Hij merkt dus wel dat er 4 comments zijn (blijkbaar), maar geeft ze niet weer. Nog een duw graag

Bovendien moet het resultaat geordend worden op "created" (is een timestamp). In bovenstaande query wordt expliciet op de story timestamp geordend, maar de comments die tussen twee stories in gecreeerd zijn, moeten in de resultset ook daartussen geplaatst worden. Kan ik dan ordenen op s.created AND com.created?

[ Voor 23% gewijzigd door Reveller op 05-06-2006 19:32 ]

[ripexx]

Deze is wel heel makelijk en ween beetje zoeken op mysql.com had ook wel geholpen

SQL:

SELECT s.* , u.name, c.url, com.*
FROM stories s, users u, categories c
LEFT JOIN comments com ON(s.sid = com.sid)
WHERE s.uid = u.uid AND s.category = c.cid
ORDER BY s.created DESC, com.created DESC

verder is de suntax goed, dus zouden de comments gewoon getoond moeten worden. Waarin voer je de query uit? Met phpmyadmin, MySQL Query Browser, mysql commandline app?

Ik zou zeggen download eens MySQL query browser en probeer het daar eens mee?

[Reveller]

ripexx schreef op maandag 05 juni 2006 @ 19:42:
[...]
verder is de suntax goed, dus zouden de comments gewoon getoond moeten worden. Waarin voer je de query uit? Met phpmyadmin, MySQL Query Browser, mysql commandline app? Ik zou zeggen download eens MySQL query browser en probeer het daar eens mee?

Met PHPMyAdmin. Heb voor de zekerheid ook die Query Browser gedownload. Gaf hetzelfde resultaat, maar ik keek niet goed: er waren meer kolommen in het resultaat dan mijn scherm breed is. Ik zag niet dat er een horizontale scrollbar zat

Maargoed - dan nog is het resultaat niet wat ik zou willen hebben. Voor de duidelijkheid hier de tabellen stories en comments (niet-relevante kolommen ingeklapt):





Het resultaat is als volgt:



Het resultaat wat ik wil hebben, zou echter moeten zijn:

code:

sid | cid | type    | title      | body       | created    | uid
----+-----+---------+------------+------------+------------+----
84  |     | article | Nieuwe web | <p>Ik bouw | 1147519549 | 1
86  |     | article | Meertalige | Vanaf nu o | 1144689371 | 1
87  |     | blog    | Poezewies  | <p>Hirsi a | .          |
88  |     | blog    | Het ken ne | .          | .          |
89  |     | blog    | NS-quiz me | .          | .          |
90  |     | blog    | Foto Fuck  | .          | .          |
82  |     | article | Bugreports | .          | .          |
83  |     | article | Commentaar | .          | .          |
    | 1   | comment |            | This is st | .          |
    | 2   | comment |            | Zal mij be | .          |
    | 3   | comment |            | Het onderz | .          |
    | 5   | comment |            | .          | .          |
    | 6   | comment |            | .          | .          |
    | 7   | comment |            | .          | .          |
    | 8   | comment |            | .          | .          |
    | 10  | comment |            | .          | .          |
    | 11  | comment |            | .          | .          |
    | 13  | comment |            | .          | .          |
    | 14  | comment |            | .          | .          |
    | 15  | comment |            | Het is ook | 1138192515 |

Op de plaats van de puntjes staat normaal content (maar alles overtypen hier heeft niet veel zin). Kan iemand mij op weg helpen en uitleggen waar het fout gaat? Moet ik de query aanpassen of moet ik de resultset in PHP bewerken?

[markvt]

hmm je wilt in principe 2 resultsets achter elkaar dus ?

[Reveller]

Nou, een gecombineerde resultset uit twee verschillende tabellen eigenlijk

[ripexx]

Maar wat jij hier toont gaat je nooit lukken met een statement. Bij een join krijg je het resultaat zoals nu getoond wordt. Indien er geen, een of meerdere comments zijn, zal je voor elke comment de bij behorende story gegevens zien.

Jij wil nu een comment gelijk stellen aan een story en omdat ze toevallig dezelfde colomnaam hebben wil je hierop sorteren. De resultset die je nu toont kan nooit, zo bevat de tabel stories geen colom type.

Wat jij wil gaat niet lukken met je huidige datamodel zonder extra code toe te voegen. Maar je wil dit soort resultset bij grote databases ook niet gaan verwerken in php. Waarom splits je het niet op in twee overzichten. Waarom in een overzicht?

[Reveller]

ripexx schreef op maandag 05 juni 2006 @ 21:56:
Waarom in een overzicht?

Ik wil een chronologische lijst geven van alle gesubmitte content op de website. Later wil ik dit per user kunnen doen. Op die manier krijg je te zien welke comments, forum topics, pagina's, weblog posts etc. een user in de tijd dat hij lid van de site is, gesubmit heeft.

* Misschien is het een beter idee om de comments en story tabel te integreren tot 1 tabel *