[PHP & MSSQL] Query met meest recente en unieke waarden

[Verwijderd]

Ik ben een applicatie aan het maken voor een soort uitzendbureau. Nu loop ik vast op een SQL-query (voor SQL Server) voor het volgende:

Het is de bedoeling dat op een pagina waar de gegevens van 1 klant staan, een overzicht wordt weergeven van alle personeelsleden (uitzendkrachten) die ooit zijn ingezet voor de betreffende klant.


Ik maak gebruik van onderstaande tabellen en kolommen voor deze pagina

<Personeel>
personeel_id, (PK)
achternaam,
voornaam

<Klanten>
klant_id, (PK)
naam

<Opdrachten>
opdracht_id, (PK)
klant_id, (FK klanten)
personeel_id, (FK personeel)
opdracht
startdatum
einddatum


De weergave op het scherm van elk personeelslid + opdracht is als volgt:

voornaam,achternaam,opdracht,startdatum,einddatum

Een simpele query die alle personeelsleden laat zien die ooit hebben voor de klant hebben gewerkt, lukt wel:

SELECT
o.*,
p.voornaam,
p.achternaam
FROM
opdrachten o,
personeel p
WHERE
o.klant_id = $klant_id
AND p.personeel_id = o.personeel_id
ORDER BY
p.voornaam,
p.achternaam,
o.einddatum

Het kan echter voorkomen dat een personeelslid meerdere keren bij deze klant heeft gewerkt aan een andere opdracht. In dat geval moet alleen de laatste opdracht (via de einddatum) weergeven worden.

De SQL-query moet ook nog eens gesorteerd kunnen worden op de vijf kolommen;
[voornaam][achternaam][opdracht][startdatum][einddatum]

Ik krijg dit niet gedaan met een GROUP BY of DISTINCT.

Kunnen jullie mij s.v.p. helpen?

[NMe]

Waarom werkt een GROUP BY personeel_id niet?

[Verwijderd]

NMe84 schreef op 31 mei 2004 @ 20:44:
Waarom werkt een GROUP BY personeel_id niet?

Om deze reden:

Warning: mssql_query(): message: Column 'o.opdracht_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16) in D:\LIP\www\secure\customers_details_iframe.php on line 74

[NMe]

Verwijderd schreef op 31 mei 2004 @ 20:48:
[...]


Om deze reden:

Warning: mssql_query(): message: Column 'o.opdracht_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16) in D:\LIP\www\secure\customers_details_iframe.php on line 74

En als je daar dan ook nog eens op groupt?

[Verwijderd]

NMe84 schreef op 31 mei 2004 @ 20:49:
[...]

En als je daar dan ook nog eens op groupt?

Dan krijg je dezelfde melding voor de volgende kolom

Gebruik momenteel the volgende SQL BTW:

SELECT o.*, p.voornaam AS Expr1, p.achternaam AS Expr2
FROM opdrachten o INNER JOIN
personeel p ON o.personeel_id = p.personeel_id
WHERE (o.klant_id = 2)

[ Voor 37% gewijzigd door Verwijderd op 31-05-2004 20:52 ]

[whoami]

NMe84 schreef op 31 mei 2004 @ 20:44:
Waarom werkt een GROUP BY personeel_id niet?

Een group by gebruik je enkel en alleen als je aggregated functies gebruikt (SUM, COUNT, AVG, ...). De velden die in je SELECT list staan, en die geen aggregated function zijn, moeten dan in je group by clausule komen.
Ik zie hier maar al te vaak dat mensen die GROUP BY onjuist gebruiken; IMO de schuld van MySQL die daar niet goed mee omspringt.

Topicstarter

Je zult dus ongeveer zo'n query moeten bouwen:

code:

SELECT p.achternaam, p.voornaam, k.naam, MAX (o.einddatum)
FROM personeel p, klanten k, opdrachten o
WHERE o.klantid = k.klantid
AND p.personeelid = o.personeelid

[ Voor 21% gewijzigd door whoami op 31-05-2004 20:58 ]

[Verwijderd]

whoami schreef op 31 mei 2004 @ 20:56:
[...]


Een group by gebruik je enkel en alleen als je aggregated functies gebruikt (SUM, COUNT, AVG, ...). De velden die in je SELECT list staan, en die geen aggregated function zijn, moeten dan in je group by clausule komen.
Ik zie hier maar al te vaak dat mensen die GROUP BY onjuist gebruiken; IMO de schuld van MySQL die daar niet goed mee omspringt.

Heb een MySQL achtergrond dus dat ik wel redelijk terecht.
Maar hoe kan ik dit nu oplossen?

[whoami]

Check m'n edit.

[Verwijderd]

whoami schreef op 31 mei 2004 @ 20:58:
Check m'n edit.

De query:

SELECT p.achternaam, p.voornaam, k.bedrijf, MAX (o.einddatum)
FROM personeel p, klanten k, opdrachten o
WHERE
k.klant_id = 2
AND o.klant_id = k.klant_id
AND p.personeel_id = o.personeel_id

geeft de volgende melding:

Column 'p.achternaam' is invallid in the SELECT list because it is not contained in an aggregate function and there is no GROUP BY clause

[ Voor 6% gewijzigd door Verwijderd op 31-05-2004 21:08 ]

[gorgi_19]

Column 'p.achternaam' is invallid in the SELECT list because it is not contained in an aggregate function and there is no GROUP BY clause

Deze foutmelding lijkt me toch redelijk duidelijk? Ik neem aan dat je hier zelf wel iets mee kan.

[ Voor 11% gewijzigd door gorgi_19 op 31-05-2004 21:11 ]

[whoami]

Euh, ja, ik ben de group by clausule dus vergeten.
Check m'n vorige post en je weet zelf ook wel wat er in die group by moet komen:
Alle velden die dus in je select list staan, en geen aggregated functie zijn dus. Die group by moet er dus zo uit zien:

code:

GROUP BY p.achternaam, p.voornaam, k.bedrijf

[Verwijderd]

whoami schreef op 31 mei 2004 @ 21:12:
Euh, ja, ik ben de group by clausule dus vergeten.
Check m'n vorige post en je weet zelf ook wel wat er in die group by moet komen:
Alle velden die dus in je select list staan, en geen aggregated functie zijn dus. Die group by moet er dus zo uit zien:

code:

GROUP BY p.achternaam, p.voornaam, k.bedrijf

Ik gebruik momenteel:

SELECT p.achternaam + ' ' + p.voornaam AS personeelslid, k.bedrijf, MAX(o.einddatum) AS Expr1
FROM opdrachten o INNER JOIN
klanten k ON o.klant_id = k.klant_id INNER JOIN
personeel p ON o.personeel_id = p.personeel_id
WHERE (k.klant_id = 2)
GROUP BY p.achternaam, p.voornaam, k.bedrijf

Dit gaat prima tot ik de andere kolommen (o.opdracht, o.startdatum) in de SQL plaats:

SELECT p.achternaam + ' ' + p.voornaam AS personeelslid, k.bedrijf, o.opdracht, o.startdatum, MAX(o.einddatum) AS Expr1
FROM opdrachten o INNER JOIN
klanten k ON o.klant_id = k.klant_id INNER JOIN
personeel p ON o.personeel_id = p.personeel_id
WHERE (k.klant_id = 2)
GROUP BY p.achternaam, p.voornaam, k.bedrijf, o.opdracht, o.startdatum

Dan krijg ik namelijk weer alle regels ipv. alleen de meest recente.
Ik begrijp alleen niet waarom!? en hoe ik dit moet oplossen.

[whoami]

Euh, dat ziet er nochtans goed uit.

Wat als je je group by zo schrijft (voor de 2de query):

code:

GROUP BY personeelslid, k.bedrijf, o.opdracht, o.startdatum

[Verwijderd]

whoami schreef op 31 mei 2004 @ 21:51:
Euh, dat ziet er nochtans goed uit.

Wat als je je group by zo schrijft (voor de 2de query):

code:

GROUP BY personeelslid, k.bedrijf, o.opdracht, o.startdatum

dan krijg ik de melding: Invallid column name 'personeelslid'

Toevoeging:

Ik heb trouwens stap voor stap de query gewijzigd om er achter te komen waar de "problemen" onstaan. Alles gaat nog goed bij het wijzigen van

p.achternaam, p.voornaam >>>> naar>>>>> p.voornaam + ' ' + p.achternaam AS personeelslid.

De voledige selectie wordt pas weergeven zodra ik in de SELECT "opdracht" en "startdatum" er bij plaats.

[ Voor 37% gewijzigd door Verwijderd op 31-05-2004 21:56 ]

[MBV]

@whoami: Maar je zei daarnet dat dat niet mocht? Ik volg jouw niet. Ik wilde dat net voorstellen toen ik dat zag, totdat jij zei dat dat een mysql-fout was. Maar het lijkt me dat hij zo wel gaat werken ja

edit:
misschien o.personeelslid? Wij geven alleen de hint, mag je zelf het vuile debugwerk doen

[ Voor 21% gewijzigd door MBV op 31-05-2004 21:56 ]

[whoami]

Ah, nee.
Het is omdat de waarden voor opdracht en startdatum anders zullen zijn. Er wordt daar dan nl. ook op gegroepeert.
Je kunt dat eventueel met een correlated subquery oplossen:

code:

select p.achternaam, p.voornaam, k.bedrijf,  max(o.einddatum)
           ( SELECT o1.opdracht, o1.startdatum 
              FROM opdrachten o1
              WHERE o1.opdracht_id = o.opdracht_id )
FROM personeel p, klanten k, opdrachten o
WHERE ....
GROUP BY p.achternaam, p.voornaam, k.bedrijf

[whoami]

MBV schreef op 31 mei 2004 @ 21:55:
@whoami: Maar je zei daarnet dat dat niet mocht? Ik volg jouw niet. Ik wilde dat net voorstellen toen ik dat zag, totdat jij zei dat dat een mysql-fout was. Maar het lijkt me dat hij zo wel gaat werken ja

edit:
misschien o.personeelslid? Wij geven alleen de hint, mag je zelf het vuile debugwerk doen

Op wat heb je het nu? Ik dacht dat het aan die alias ging liggen, maar dat is het dus niet.
Het is gewoon omdat er -logischerwijze- voor de velden opdracht en begindatum andere waarden bestaan. Er wordt dus daar ook op gegroupeerd.

[Verwijderd]

whoami schreef op 31 mei 2004 @ 21:56:
Ah, nee.
Het is omdat de waarden voor opdracht en startdatum anders zullen zijn. Er wordt daar dan nl. ook op gegroepeert.
Je kunt dat eventueel met een correlated subquery oplossen:

code:

select p.achternaam, p.voornaam, k.bedrijf,  max(o.einddatum)
           ( SELECT o1.opdracht, o1.startdatum 
              FROM opdrachten o1
              WHERE o1.opdracht_id = o.opdracht_id )
FROM personeel p, klanten k, opdrachten o
WHERE ....
GROUP BY p.achternaam, p.voornaam, k.bedrijf

Dan gaat MS SQL weer zeuren dat "o.opdracht_id" niet in de GROUP BY staat.
Dus voeg ik deze ook toe en dan krijg ik weer alle rijen!

[whoami]

Hmm, die opdracht_id staat toch nergens in je SELECT list?
Nou goed, ik heb geen zin om het uit te testen.

Je kan het nog altijd oplossen dmv een subquery, al is dat wat ranziger vind ik:

code:

select p.achternaam, ...., o.einddatum, o.opdracht, o.startdatum
FROM personen p, opdrachten o, klanten k
WHERE p.persoon_id = o.persoon_id
AND o.klant_id = k.klant_id
AND klant_id = 2
AND einddatum = (SELECT MAX (einddatum) FROM opdrachten WHERE opdrachten.klant_id = k.klant_id )

Nouja, zo ongeveer dan. Er kunnen fouten enzo inzitten; ik ben brak.

[ Voor 7% gewijzigd door whoami op 31-05-2004 22:08 ]

[Verwijderd]

Ik ben er bijna, maar nog niet helemaal!

Nieuwe query met subselect:

code:

SELECT 
    o.*, 
    p.voornaam + ' ' + p.achternaam AS personeelslid,
    p.*
FROM
    opdrachten o,
    personeel p
WHERE
    o.klant_id = $klant_id
    AND p.personeel_id = o.personeel_id
    AND o.einddatum = 
        (SELECT 
            MAX(e.einddatum) 
        FROM 
            opdrachten e 
        WHERE 
            e.personeel_id=o.personeel_id)

Geeft maar 1 personeelslid, namelijk het personeelslid die de laatste (hoogste) einddatum heeft. Dus niet de laatste opdracht van elk personeelslid die ooit voor deze klant heeft gewerkt.

Als je dan "o.klant_id = $klant_id" verwijderd, krijg je van alle personeelsleden in de tabel (dus niet alleen die een opdracht hebben gehad bij de betreffende klant) de laatste opdracht.

oftewel;

code:

SELECT 
    o.*, 
    p.voornaam + ' ' + p.achternaam AS personeelslid,
    p.*
FROM
    opdrachten o,
    personeel p
WHERE   
    p.personeel_id = o.personeel_id
    AND o.einddatum = 
        (SELECT 
            MAX(e.einddatum) 
        FROM 
            opdrachten e 
        WHERE 
            e.personeel_id=o.personeel_id)

Ik ben er dus bijna. Hoe zorg ik er voor dat SQL-query 2 alleen de opdrachten laat zien voor de betreffende klant zonder dat ik het resultaat krijg van Query 1???

----------------------------------edit---------------------------------------

Goed, was iets te snel met mijn post, zie hier de oplossing:

code:

SELECT 
    o.*, 
    p.voornaam + ' ' + p.achternaam AS personeelslid,
    p.*
FROM
    opdrachten o,
    personeel p
WHERE   
    p.personeel_id = o.personeel_id
    AND o.einddatum = 
        (SELECT 
            MAX(e.einddatum) 
        FROM 
            opdrachten e 
        WHERE 
            e.personeel_id=o.personeel_id
            AND e.klant_id=$klant_id
        )

[ Voor 26% gewijzigd door Verwijderd op 01-06-2004 12:31 . Reden: Ben er al uit ]