[SQL] probleem met query (joins)

[Gertjan]

Ik ben bezig een systeem te maken waarmee voor een bedrijf waarmee leads of prospects vastgelegd kunnen worden: mogelijke klanten dus. Voor deze prospects moeten meerdere activiteiten (gemaild, gebeld, enz) vastgelegd kunnen worden. Om een makkelijk overzicht te kunnen krijgen, wil ik een lijst kunnen genereren van alle prospects, met de datum waarop de laatste activiteit uitgevoerd is.
Het relevante gedeelte van de database ziet er als volgt uit:



Met onderstaande query krijg ik een lijst van alle prospects, met daarbij de datum van de laatste activiteit, of NULL:

code:

SELECT prospects.prospect_id, bedrijfsnaam, date_format( activities.date, '%e %b %Y' ) AS date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
GROUP BY prospects.prospect_id
ORDER BY unix_timestamp( activities.date ) ASC

Nu moet er echter ook een search-functie inkomen, waarmee bepaalde selecties gemaakt kunnen worden. Dit werkt allemaal perfect, op 1 onderdeel na: het maken van een selectie, gebaseerd op de datum van de laatste activiteit.

Stel: ik heb een prospect met meerdere activiteiten, waarbij de laatste van gisteren was (7-7-05), en de eerste van 4-7-05. Als ik nu ga zoeken op prospects waarbij de datum van de laatste activiteit voor 6-6-05 ligt, wil ik vanzelfsprekend die genoemde prospect niet zien. Dat gebeurt echter wel.
De query die ik gebruik ik als volgt:

code:

SELECT prospects.prospect_id, bedrijfsnaam, date_format(activities.date, '%e %b %Y') AS date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
WHERE unix_timestamp( activities.date ) < unix_timestamp( '2005-7-6' )
GROUP BY prospects.prospect_id

Het resultaat hiervan is (bijvoorbeeld):
6 Bedrijf1 4 Jul 2005

terwijl ik dit wil zien:
6 Bedrijf1 7 Jul 2005

Heeft iemand enig idee wat ik verkeerd doe in mijn query?
Het gaat btw om MySQL, versie < 4.1, dus subqueries is geen optie.

[curry684]

SQL:

SELECT prospects.prospect_id, bedrijfsnaam, date_format( activities.date, '%e %b %Y' ) AS date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
GROUP BY prospects.prospect_id
ORDER BY unix_timestamp( activities.date ) ASC

Fix die query eens

quote: http://gathering.tweakers...t_messages/741009#groupby

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

[Verwijderd]

Is dit misschien een oplossing?

<code>
SELECT prospects.prospect_id, bedrijfsnaam, date_format(activities.date, '%e %b %Y') AS date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
GROUP BY prospects.prospect_id
HAVING unix_timestamp( activities.date ) < MAX(unix_timestamp( '2005-7-6' ))
</code>

[Janoz]

Je hebt een group by gebruikt, terwijl er nog velden zijn die daarbij niet worden genoemd, maar zonder aggregated function worden gebruikt. In een normaal database systeem wordt hierop een error gegeven. MySQL probeert maar wat met dit soort dingen als gevolg.

Ik raad je aan om in de P&W faq even het stukje over group by op te zoeken. Hierin staat waarschijnlijk wel info die je probleem op zal lossen.

[Gertjan]

Bedankt voor de hulp zover. De eerste query heb ik nu aangepast, zodat die fout met de 'order by' nu weg is. Dat is nu deze code geworden:

SQL:

SELECT prospects.prospect_id, prospects.bedrijfsnaam, MAX( DATE_FORMAT( activities.date, '%e %b %Y' )) AS date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
GROUP BY prospects.prospect_id, prospects.bedrijfsnaam
ORDER BY prospect_id ASC

De query voor de zoekfunctie kom ik echter niet uit. Als ik, zoals flbos aanraadde, gebruik maak van een having-clause, krijg ik nog steeds niet de resultaten die ik zoek.

SQL:

SELECT prospects.prospect_id, prospects.bedrijfsnaam, MAX( DATE_FORMAT( activities.date, '%e %b %Y' ) ) AS activity_date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
GROUP BY prospects.prospect_id, prospects.bedrijfsnaam
HAVING UNIX_TIMESTAMP( activity_date ) < UNIX_TIMESTAMP( '2005-7-7' )
ORDER BY prospect_id ASC

Resultaten hiervan zien er bijvoorbeeld zo uit:

code:

prospect_id bedrijfsnaam    activity_date
1           bedrijf_1       7 Jul 2005
2           bedrijf_2       7 Jul 2005
6           bedrijf_3       8 Jul 2005
7           bedrijf_4       6 Jul 2005
8           bedrijf_5       7 Jul 2005

/edit
Crap ...

SQL:

SELECT prospects.prospect_id, prospects.bedrijfsnaam, MAX( DATE_FORMAT( activities.date, '%e %b %Y' ) ) AS activity_date
...
HAVING UNIX_TIMESTAMP( activity_date ) < UNIX_TIMESTAMP( '2005-7-7' )

MySQL herkende de datum in dit formaat niet meer correct, en maakte er dus een andere timestamp van dan verwacht...

Zo lijkt hij goed te werken:

SQL:

SELECT prospects.prospect_id, prospects.bedrijfsnaam, max( unix_timestamp( activities.date ) ) AS activity_date
FROM prospects
LEFT JOIN activities ON activities.prospect_id = prospects.prospect_id
GROUP BY prospects.prospect_id, prospects.bedrijfsnaam
having activity_date < unix_timestamp('2005-7-7')
ORDER BY prospect_id ASC

Iemand nog op- of aanmerkingen?

[ Voor 31% gewijzigd door Gertjan op 08-07-2005 14:12 ]