[SQL] Join euvel

[Heidistein]

Hey,
Ik wordt zo langzamerhand een beetje radeloos. Ik doe iets fout, maar ik snap niet waar.
Ik probeer een (MySQL) query te doen met een join over 3 tabellen. Bin there, done that before.
Deze qeury haalt /alle/ klantinformatie (Klanten.*) op, de laatste keer dat er contact geweest is (BelEvent.Tijd), en wat de status toen geworden is (Status.Naam).

Klanten.KlantID is een FQ op BelEvent.KlantID
BelEvent.StatusID is een FQ op Status.StatusID.

De query:

SELECT `Klanten` . * , `Status`.`Naam` AS
Status , `BelEvent`.`Tijd` AS Tijd
FROM (
  (
    `BelEvent`
    RIGHT JOIN `Klanten` ON `BelEvent`.`KlantID` = `Klanten`.`KlantID`
  )
  LEFT JOIN `Status` ON `BelEvent`.`StatusID` = `Status`.`StatusID`
);

resultaat hiervan is:

+---------+--------------+----------------+------------------+------------------+--------------------------+----------+--------+-------+---------+----------------+---------------------+
| KlantID | Bedrijfsnaam | Contactpersoon | Telnummer        | Faxnummer        | Adres                    | Postcode | Plaats | eMail | Website | Status         | Tijd                |
+---------+--------------+----------------+------------------+------------------+--------------------------+----------+--------+-------+---------+----------------+---------------------+
|       1 | Uman         | Arjen Heidinga | 0515-444445      | 0515-nogwat      | Koperslagersstraat 82-84 |          |        |       |         | NULL           |                NULL | 
|       2 | test         | jan-jurk       | 90329058234      | 023948023498     | asf awfa wf4f a 332      |          |        |       |         | Wel interesee  | 2008-09-02 12:16:39 | 
|       2 | test         | jan-jurk       | 90329058234      | 023948023498     | asf awfa wf4f a 332      |          |        |       |         | Geen interesse | 2008-09-02 12:19:12 | 
|       2 | test         | jan-jurk       | 90329058234      | 023948023498     | asf awfa wf4f a 332      |          |        |       |         | Geen interesse | 2008-09-02 16:21:03 | 
|       3 | Vink Lisse   | Piet Punthoofd | 2998 9342-083249 | 9390248u 93248-0 | takktlaan 1              |          |        |       |         | NULL           |                NULL | 
+---------+--------------+----------------+------------------+------------------+--------------------------+----------+--------+-------+---------+----------------+---------------------+
5 rows in set (0.00 sec)

Okee, dat wil ik. Nu wil ik hiervan de MAX(Tijd). Wat doet men dan? (zou ik zeggen):

SELECT `Klanten` . * , `Status`.`Naam` AS
Status , MAX(`BelEvent`.`Tijd`) AS Tijd
FROM (
  (
    `BelEvent`
    RIGHT JOIN `Klanten` ON `BelEvent`.`KlantID` = `Klanten`.`KlantID`
  )
  LEFT JOIN `Status` ON `BelEvent`.`StatusID` = `Status`.`StatusID`
) GROUP BY KlantID;

Effect is:

+---------+--------------+----------------+------------------+------------------+--------------------------+----------+--------+-------+---------+---------------+---------------------+
| KlantID | Bedrijfsnaam | Contactpersoon | Telnummer        | Faxnummer        | Adres                    | Postcode | Plaats | eMail | Website | STATUS        | Tijd                |
+---------+--------------+----------------+------------------+------------------+--------------------------+----------+--------+-------+---------+---------------+---------------------+
|       1 | Uman         | Arjen Heidinga | 0515-444445      | 0515-nogwat      | Koperslagersstraat 82-84 |          |        |       |         | NULL          |                NULL | 
|       2 | test         | jan-jurk       | 90329058234      | 023948023498     | asf awfa wf4f a 332      |          |        |       |         | Wel interesee | 2008-09-02 16:21:03 | 
|       3 | Vink Lisse   | Piet Punthoofd | 2998 9342-083249 | 9390248u 93248-0 | takktlaan 1              |          |        |       |         | NULL          |                NULL | 
+---------+--------------+----------------+------------------+------------------+--------------------------+----------+--------+-------+---------+---------------+---------------------+

Als je goed kijkt dan zie je bij status 'Wel interesse', daar moet 'Geen interesse' staan eigenlijk, zie de query erboven.
Ik heb al werkelijk van alles geprobeerd, herschikken van de joins, twee keer een left join, een inner join gebruiken, haken veranderen, sorteren op zaken, de order by veranderen.

Wat ik graag wil weten is: Hoe doen we dit goed, maar wat ik meer wil weten is wat mijn denkfout is. Waarom doet MySQL zomaar dit veranderen?

[Creepy]

MySQL verpest het voor je omdat het weigert in dit geval je een foutmelding te geven. Van elk andere DBMS had je nu een foutmelding kregen met een melding dat je velden bent vergeten te grouperen. MySQL geeft je nu @random een waarde terug. Zie ook Programming FAQ - SQL

[Heidistein]

Okay, ik snap het. Volgende vraag, is het eigenlijk wel mogelijk wat ik wil? Ik begin een beetje aande uitdaging, en mezelf, te twijfelen.... Ik meende dat ik me kon redden met sql

[Voutloos]

Ja, het kan. Je moet gewoon zorgen dat de rijen waar je tegen aan joint bij diezelfde max Tijd horen. Oftewel een groupwise maximum (==gegarandeerd raak in google).

[Heidistein]

AHA! Ik heb weer iets geleerd!
Heilig, en kijk naar mijn huge ass query! Het kan ongetwijfelt korter, maar dan kan mijn hoofd het niet meer

Voutloos en Creepy, credits!
Groupwise sorting... Ik had MySQL slimmer ingeschat... PEBKEC again

SQL:

SELECT `tmp1` . *
FROM (
    SELECT `Klanten`. * , `Status`.`Naam` AS MyStatus, `BelEvent`.`Tijd` AS `Tijd`
    FROM `BelEvent`
        RIGHT JOIN `Klanten` ON `BelEvent`.`KlantID` = `Klanten`.`KlantID`
        LEFT JOIN `Status` ON `BelEvent`.`StatusID` = `Status`.`StatusID`
    ) `tmp1`
LEFT JOIN (
    SELECT `Klanten`. * , `Status`.`Naam` AS MyStatus, `BelEvent`.`Tijd` AS `Tijd`
    FROM `BelEvent`
        RIGHT JOIN `Klanten` ON `BelEvent`.`KlantID` = `Klanten`.`KlantID`
        LEFT JOIN `Status` ON `BelEvent`.`StatusID` = `Status`.`StatusID`
    ) `tmp2` ON `tmp1`.`KlantID` = `tmp2`.`KlantID`
AND `tmp1`.`Tijd` < `tmp2`.`Tijd`
WHERE `tmp2`.`KlantID` IS NULL

[ Voor 6% gewijzigd door Heidistein op 03-09-2008 11:10 ]

[Voutloos]

Dit is idd 1 vd oplossingen.

Een GROUPORDER BY clause, voor het sorteren van rows voor het groupen zou van tijd tot tijd een onwijs geile feature zijn, maar er zijn wel meer dbms'en die het niet kunnen.

Enne, het is PEBKAC.