[SQL] groeps vergelijking..."onmogelijke" query ?

[Verwijderd]

Hallo,

Ik heb al gezocht in SQL boeken, op google en op GOT maar voor dit probleem is het moeilijk om een oplossing te vinden....ik hoop dat iemand kan helpen....

Ik heb een de volgende tabellen:

naam: wf_assignment

code:

id    project_id
--    ----------
11    1
12    1
13    1
14    2

naam: wf_assignment_procedure

code:

id    assignment_id   set_bool
--    -------------   --------
1     11              true
2     11              true

3     12              true
4     12              false

5     13              false
6     13              false

7     14              true
8     14              true

Ik heb nu deze query:

code:

SELECT wfa.*
FROM wf_assignment wfa
LEFT JOIN wf_assignment_procedure wfap ON wfap.assignment_id = wfa.id
WHERE wfa.project_id = '1'
      AND wfap.set_bool = 'true'
GROUP BY id

En krijg van deze query het volgende resultaat:

code:

id    project_id
--    ----------
11    1
12    1

maar wat ik eingelijk als resultaat wil zien is:

code:

id    project_id
--    ----------
11    1

Dus wat ik eigenlijk wil is alle wf_assignments waarvan alle wf_assignment_procedure de waarde set_bool op true hebben staan...in dit geval alleen 11 en niet 11 en 12 zoals in de huidige query.

Weet iemand de juiste query voor deze oplossing!
Alvast bedankt!!!

ps. ik gebruik MySQL versie 4.0.21 (dus ondersteunt geen sub-query's)

[ Voor 6% gewijzigd door Verwijderd op 04-11-2004 11:36 ]

[Reinier]

Misschien een UNIQUE DISTINCT over je ID heengooien? Dus iets als:

code:

SELECT DISTINCT id
FROM ...

offtopic:
Whehehe, wfap wfap wfap

[ Voor 37% gewijzigd door Reinier op 04-11-2004 11:14 ]

[thesvr]

Voor alle duidelijkheid set_bool mag dus niet false zijn?

Dan kan je beter wfap.set_bool<> 'false' gebruiken?

Of wil je alleen het project_id hebben als set_bool true is?

[Verwijderd]

Dat resultaat van de query is absoluut logisch. Je kan wel even wat meer informatie geven...

Met alleen het project_id krijg je altijd twee resultaten. Als je ook nog het id van de wf_assignment tabel meegeeft, dan krijg je alleen je gewenste resultaat.

Ik twijfel alleen aan de opzet van dit databasemodel. Kan je uitleggen wat je precies wil opslaan en wat alles voorstelt?

[Verwijderd]

Inderdaad...set_bool MOET voor alle wf_assignment_procedures op 'true' staan voor een wf_assignment.

Ik wil dus als resultaat de volgende records hebben(dus niet alleen het project_id):

code:

id    project_id 
--     ----------
11    1

(er zijn nog veel meer gegevens beschikbaar, maar voor de duidelijkheid heb ik alleen de nodige velden weergegeven)

[thesvr]

Ik denk ook dat het dbmodel niet echt optimaal is voor deze query.

Het is ongetwijfeld wel mogelijk (eventueel met subquery ed) maar het wordt dan wel een langzame, dus als het veel gebruikt gaat worden kan je misschien beter wat gaan aanpassen in het model.

[Reinier]

Werkt mijn DISTINCT oplossing niet dan?
Ik zal wel iets over het hoofds zien.

[Verwijderd]

de DISTINCT optie is niet nodig omdat ik al een GROUP functie gebruik.

En dan nog krijg ik wf_assignment's terug waarvan sommige wf_assignment_procedure's de set_bool waarde 'false' bevatten....

Het is geen optie om het database model te wijzigen, dit heeft teveel gevolgen voor de applicatie die er gebruik van maakt...

[ Voor 6% gewijzigd door Verwijderd op 04-11-2004 11:34 ]

[Verwijderd]

Zoals iedereen hier wel vermoedt is er inderdaad iets mis met je redenering. Misschien je DB-schema die niet ideaal is, maar daarvoor hier niet voldoende info.

Eigenlijk krijg je in dit resultaat twee keer dezelfde 'info' terug, wel met een verschillende id. De vraag die je stelt aan de DB via deze query geeft je gewoon terug wat je vroeg. Als je iets anders wilt moet je het anders vragen.

Waarom wil je van die tweede rij af? Da's de vraag die je je moet stellen. Misscheen zie je dan dat je schema mis zit.

Als je maar één rij wilt kan je altijd met trucjes werken zoals TOP 1 (weet niet of dit bestaat in MySQL). Er zijn nog mogelijkheden, ook zonder performantieverlies.
Maar begin eerst eens meer info te geven dan.

D.

[ACM]

Creatief met having:

SQL:

SELECT wfa.*,
   SUM(CASE wfap.set_bool WHEN true THEN 1 WHEN false THEN 0 END) as truecount,
   COUNT(wfap.*) as totalcount
FROM wf_assignment wfa
LEFT JOIN wf_assignment_procedure wfap ON wfap.assignment_id = wfa.id
WHERE wfa.project_id = '1'
GROUP BY id
HAVING truecount = totalcount AND totalcount > 0

Die sum/count kunnen eventueel ook direct in de having geloof ik.

[ Voor 3% gewijzigd door ACM op 04-11-2004 11:46 ]

[Verwijderd]

Een 9,9 voor ACM!!!!!
Dit is inderdaad DE query die ik zocht!
Dank je wel voor de oplossing!

Ik heb alleen nog een paar kleine aanpassingen moeten doen:

• true en false tussen quotes
• COUNT(wfap.id) ipv COUNT(wfap.*)

code:

SELECT wfa.*,
   SUM(CASE wfap.set_bool WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) as truecount,
   COUNT(wfap.id) as totalcount
FROM wf_assignment wfa
LEFT JOIN wf_assignment_procedure wfap ON wfap.assignment_id = wfa.id
WHERE wfa.project_id = '1'
GROUP BY id
HAVING truecount = totalcount AND totalcount > 0

Dank je wel!!!!!!

[ Voor 6% gewijzigd door Verwijderd op 04-11-2004 12:11 ]

[Verwijderd]

code:

SELECT wfa.*,
   SUM(CASE wfap.set_bool WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) as truecount,
   COUNT(wfap.id) as totalcount
FROM wf_assignment wfa
LEFT JOIN wf_assignment_procedure wfap ON wfap.assignment_id = wfa.id
WHERE wfa.project_id = '1'
GROUP BY wfa.id
HAVING truecount = totalcount AND totalcount > 0

De query hierboven werkt inderdaad voor MySQL. Echt super!!!
Nu probeer ik de query te testen onder MS SQL Server 2000 met een ORDER achter de query (ORDER BY wfa.date_time ASC), maar dan krijg ik foutmeldingen dat de aliassen truecount en totalcount ongeldig zijn:

code:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'truecount'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'totalcount'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'totalcount'.

Wat kan hier de oorzaak van zijn....?

[ Voor 6% gewijzigd door Verwijderd op 12-11-2004 11:12 ]

[jvdmeer]

Verwijderd schreef op 12 november 2004 @ 11:05:

code:

SELECT wfa.*,
   SUM(CASE wfap.set_bool WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) as truecount,
   COUNT(wfap.id) as totalcount
FROM wf_assignment wfa
LEFT JOIN wf_assignment_procedure wfap ON wfap.assignment_id = wfa.id
WHERE wfa.project_id = '1'
GROUP BY wfa.id
HAVING truecount = totalcount AND totalcount > 0

De query hierboven werkt inderdaad voor MySQL. Echt super!!!
Nu probeer ik de query te testen onder MS SQL Server 2000 met een ORDER achter de query (ORDER BY wfa.date_time ASC), maar dan krijg ik foutmeldingen dat de aliassen truecount en totalcount ongeldig zijn:

code:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'truecount'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'totalcount'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'totalcount'.

Wat kan hier de oorzaak van zijn....?

Dit heeft te maken met de scopes van de kolomnamen. In MSSQL worden de ISO normen iets beter gehanteerd dan in MySQL en zijn in de WHERE en de HAVING alleen kolomnamen uit de tabellen aanwezig niet de aliassen. Dus de aliassen vervangen levert het gewenste effect:

code:

SELECT wfa.*,
   SUM(CASE wfap.set_bool WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) as truecount,
   COUNT(wfap.id) as totalcount
FROM wf_assignment wfa
LEFT JOIN wf_assignment_procedure wfap ON wfap.assignment_id = wfa.id
WHERE wfa.project_id = '1'
GROUP BY wfa.id
HAVING 
  SUM(CASE wfap.set_bool WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) = COUNT(wfap.id) AND 
  COUNT(wfap.id)> 0