[MySQL] Grouperen op een veld; maar één uniek record terug

[NMe]

Sorry voor de vage topictitel, maar dat is de beste manier waarop ik kan omschrijven waar ik naartoe wil.

Situatie
Ik heb een tweetal tabellen die relevant zijn voor dit probleem:
mdb_movie:
movie_id, movie_name, movie_genre, ... , enz

mdb_loan:
loan_id, loan_movie_id, loan_loaner_id, loan_date_loaned, loan_date_returned

loan_movie_id is referentiëel, en verwijst uiteraard naar de movie-tabel, en loan_loaner_id verwijst naar een andere tabel waar alle mensen in staan die een DVD kunnen lenen, maar die tabel is hier niet relevant.

Wanneer loan_date_returned de waarde NULL bevat, dan is een film uitgeleend; wanneer het veld een datum bevat, is de film teruggebracht.

Wat ik wil doen
Ik wil graag in mijn filmdatabase een lijst weergeven van al mijn films, waarbij alle films die ik op dit moment uitgeleend heb, dik gedrukt worden. Op zich geen probleem dacht ik, sterker nog, ik dacht dat ik het werkend had, maarrrrr:

Het probleem
Films die ik vaker dan één keer uitgeleend heb, komen ook vaker dan één keer terug in mijn filmlijst. Hier zie je een voorbeeld: de eerste film staat slechts één keer in mijn database, maar wordt twee keer weergegeven omdat ik mijn GROUP BY niet netjes krijg.

Mijn query ziet er als volgt uit:

SQL:

SELECT         mdb_movie.*,
               mdb_loan.loan_id,
               mdb_loan.loan_date_returned
FROM           mdb_movie
LEFT JOIN      mdb_loan ON movie_id = mdb_loan.loan_movie_id
GROUP BY       movie_name, loan_date_returned

Deze query heb ik enigszins versimpeld. Normaal zit er nog een order by clause bij, die sorteert op het veld dat de gebruiker gekozen heeft, en een limit clausule.

Nu is het probleem vrij logisch, aangezien ik groepeer op naam, en daarna ook op date_returned, maar als ik date_returned niet opneem in mijn query, dan krijg ik alleen de gegevens van de eerste keer dat een film is uitgeleend, terwijl ik juist de laatste wil hebben. Ik kan niets met order by doen, omdat er eerst gegroepeerd wordt, en daarna pas gesorteerd. Inner joins zijn ook zinloos omdat ik dan films die niet uitgeleend zijn niet terug krijg, en bovendien zou dat volgens mij het probleem niet oplossen.

Mijn vraag
Ziet een van jullie hoe ik mijn query in een MySQL versie zonder subqueries zo kan aanpassen dat ik maar één record per film terugkrijg, met daarin informatie over de laatste keer dat een film is uitgeleend?

[ Voor 4% gewijzigd door NMe op 18-08-2005 00:30 ]

[Meloentje]

Jij wilt een lijst van al je films in mdb_movie.
En je wilt van die film weten of die uitgeleend is, oftewel of er een record van in mdb_loan staat met een open retour datum.
Dus niet de records uit mdb_loan waarvan de retour datum is ingevuld.

SELECT mdb_movie.*,
mdb_loan.loan_id,
mdb_loan.loan_date_returned,
mdb_loan.loan_date_loaned
FROM mdb_movie
LEFT JOIN mdb_loan ON movie_id = mdb_loan.loan_movie_id
WHERE mdb_loan.loan_date_returned=NULL
GROUP BY movie_name, loan_date_returned

Wanneer loan_date_loaned NULL is dan is de film niet uitgeleend.
(mdb_loan.loan_date_returned=NULL weet niet of dit zo kan, ben niet zo thuis in MySQL)

[NMe]

Meloentje schreef op maandag 24 januari 2005 @ 16:12:
Jij wilt een lijst van al je films in mdb_movie.
En je wilt van die film weten of die uitgeleend is, oftewel of er een record van in mdb_loan staat met een open retour datum.
Dus niet de records uit mdb_loan waarvan de retour datum is ingevuld.

Jawel, ik wil beide in één recordset, en later, àls een film nog is uitgeleend, dan wil ik die film vet afdrukken. Helaas kan ik dus niets met jouw query, maar bedankt voor het meedenken.

Wanneer loan_date_loaned NULL is dan is de film niet uitgeleend.
(mdb_loan.loan_date_returned=NULL weet niet of dit zo kan, ben niet zo thuis in MySQL)

Wanneer je vergelijkt met NULL moet je de IS operator gebruiken, of de ISNULL() functie.

[ Voor 5% gewijzigd door NMe op 24-01-2005 16:15 ]

[bigbeng]

Kun je niet gewoon MAX(loan_date_loaned) gebruiken? En loan_date_loaned uit de GROUP BY halen?

[NMe]

bigbeng schreef op maandag 24 januari 2005 @ 16:22:
Kun je niet gewoon MAX(loan_date_loaned) gebruiken? En loan_date_loaned uit de GROUP BY halen?

Hoe had je dat in gedachten? Een clausule opnemen met het volgende?

SQL:

HAVING     loan_date_loaned = MAX(loan_date_loaned)

Krijg je dan niet alleen die ene film terug die het laatst is uitgeleend en de rest niet? Nouja, ik zal het even proberen, en ik hoor het graag als ik je fout begrepen heb.

Edit:
Nope, met opnemen van bovenstaande HAVING clause krijg ik alleen die films terug die ooit zijn uitgeleend en ook zijn teruggegeven. Wanneer ik er een regel aan toevoeg, heb ik hetzelfde resultaat als nu:

SQL:

HAVING     loan_date_loaned = MAX(loan_date_loaned)
OR         loan_date_loaned IS NULL

[ Voor 24% gewijzigd door NMe op 18-08-2005 00:29 ]

[P_de_B]

code:

SELECT moviename, movie_genre, MAX(l.loan_date_returned) as lastloanDate
FROM mdb_movie m
LEFT OUTER JOIN mdb_loan l  ON m.movie_id = l.movie_Id
GROUP BY  moviename, movie_genre

Als je meer kolommen in je select list opneemt, moet je ze ook aan de group by toevoegen. Als je zoals in je eigen query ook mdb_load opneemt in je select list, zal er logischerwijs voor elke keer dat een film uitgeleent is een record in je resultset zitten. Misschien nog eens het gedeelte over GROUP BY in de faq lezen?

[NMe]

P_de_B schreef op maandag 24 januari 2005 @ 16:33:
Misschien nog eens het gedeelte over GROUP BY in de faq lezen?

Argh, duh. Met al die keren dat ik mensen naar die FAQ verwezen heb zou je onderhand wel zeggen dat ik weet hoe het zit. MySQL maakt je lui.

Even uitproberen of ik het op die manier opgelost krijg. Zo niet, dan weet ik jullie te vinden.

Edit:
Helaas, nog steeds hetzelfde probleem wanneer ik correct SQL gebruik.

[ Voor 10% gewijzigd door NMe op 24-01-2005 16:44 ]

[bigbeng]

-NMe- schreef op maandag 24 januari 2005 @ 16:24:
[...]

Hoe had je dat in gedachten? Een clausule opnemen met het volgende?

SQL:

HAVING     loan_date_loaned = MAX(loan_date_loaned)

Krijg je dan niet alleen die ene film terug die het laatst is uitgeleend en de rest niet? Nouja, ik zal het even proberen, en ik hoor het graag als ik je fout begrepen heb.

Edit:
Nope, met opnemen van bovenstaande HAVING clause krijg ik alleen die films terug die ooit zijn uitgeleend en ook zijn teruggegeven. Wanneer ik er een regel aan toevoeg, heb ik hetzelfde resultaat als nu:

SQL:

HAVING     loan_date_loaned = MAX(loan_date_loaned)
OR         loan_date_loaned IS NULL

Maak van die having eens:

SQL:

HAVING     loan_date_loaned = MAX(loan_date_loaned)
OR ( loan_date loaned IS NULL AND mdb_loan.loan_movie_id NOT IS NULL )

Ongetest helaas dus geen garanties

[NMe]

loan_movie_id kan nooit NULL zijn.

[bigbeng]

left outer join, dus kan wel, toch? Als je een film nog nooit hebt uitgeleend, of komt dit niet voor?

[NMe]

Je hebt gelijk, maar het werkt niet, ook niet met jouw laatste suggestie. Ik krijg dezelfde records terug als met mijn eigen having clause die ik hierboven postte, ook wanneer ik de goeie veldnamen gebruik (ik heb per ongeluk een paar keer date_loaned geschreven waar ik date_returned bedoelde, en dat heb je overgenomen). Ik ben dus weer terug bij af.

Als ik subqueries zou kunnen gebruiken, dan was ik er waarschijnlijk zo uit. Is er geen manier om naar andere records te kijken, zonder subqueries? Een union kan overigens ook niet (het script moet werken op MySQL 3.23, en unions zijn pas ondersteund sinds versie 4.0).

Edit:
Ik zit nu zo dichtbij dat ik de oplossing kan ruiken, maar het lukt me niet.

SQL:

SELECT          mdb_movie.movie_id,
                mdb_movie.movie_name,
                mdb_movie.movie_genre,
                mdb_movie.movie_pers_rating,
                mdb_movie.movie_imdb_rating,
                mdb_movie.movie_medium,
                mdb_loan.loan_id,
                mdb_loan.loan_date_returned,
                mdb_loan.loan_date_loaned,
                MAX(mdb_loan.loan_date_returned) AS max_loaned
FROM            mdb_movie
LEFT JOIN       mdb_loan ON movie_id = mdb_loan.loan_movie_id
GROUP BY        movie_name,
                loan_date_returned,
                loan_date_loaned,
                loan_id,
                movie_id,
                movie_genre,
                movie_medium,
                movie_pers_rating,
                movie_imdb_rating
HAVING          loan_date_returned = max_loaned OR loan_date_returned IS NULL

Nu krijg ik weliswaar maar één record terug per film, maar het is altijd een record waarin de film al teruggebracht is, omdat elke datum natuurlijk groter is dan NULL. Als een film dus in het verleden uitgeleend is en weer teruggebracht, en hij wordt weer uitgeleend, dan zie ik hem toch niet bold, omdat ik de datum van de vorige uitlening terugkrijg, en niet NULL, zoals ik zou willen. Ik zou eigenlijk willen dat NULL voorrang heeft over de maximale waarde in het datumveld... Maar hoe?

[ Voor 67% gewijzigd door NMe op 18-08-2005 00:29 ]

[NMe]

Na een hele tijd tegen dit probleem aangekeken te hebben, heb ik maar besloten de quick 'n dirty methode te gebruiken om dit probleem te fixen. Ik heb een extra veld in mdb_movie opgenomen, waarin ik bijhoud of een film is uitgeleend of niet. Niet echt netjes, maar wel effectief.

Bedankt voor het meedenken in ieder geval.

[P_de_B]

Je moet met ISNULL(mdb_load.loan_date_returned,'01-01-2999') as max_loaned wel iets kunnen denk ik. Als de datum NULL is vervang je hem door een hele hoge datum.

Andere oplossingen zouden met een CASE WHEN constructie wel te doen zijn, maar ik weet niet of MySQL dat ondersteunt