[MySQL] Query probleem (joins?)

[florisje]

ik heb de volgende tabel:

code:

eigenschappen
 id  |   ding    | eigenschap
-----------------------------
 1   |     1     |     5
 2   |     1     |     6
 3   |     2     |     5
 4   |     3     |     5
 5   |     3     |     6
 6   |     4     |     2
 7   |     4     |     3

ik wil nu alle 'dingen' die de eigenschappen 5 EN 6 hebben.
het resultaat moet dus in dit geval ding 1 en 3 zijn.
het probleem is dus dat het niet met directe ANDs kan omdat het over meerdere rijen gaat. het datamodel kan niet worden aangepast en het is ook mogelijk dat er op meerdere eigenschappen getest moet worden (dus alle 'dingen' die eigenschap 5 EN 6 EN 7 EN 8 moeten hebben)
ik heb het ook geprobeerd met joins maar dan krijg je dus een join op zichzelf en dat gaat natuurlijk niet.
ik kom er echt absoluut niet uit dus ik roep jullie hulp maar in.
alvast bedankt...

[Dido]

Een join op zichzelf gaat wel, hoor. Het probleem is dat je het dynamisch wilt doen (met een variabel aantal eigenschappen) en dat wordt wel lastig. Even de hersentjes laten kraken...

Voor je eerste voorbeeld is het vrij simpel, op voorwaarde dat je MySQL (al) subqueries aankan:

code:

SELECT ding
  FROM tabel
 WHERE eigenschap = "5"
   AND ding in (SELECT ding 
                  FROM tabel 
                 WHERE eigenschap = "6");

Hoe werkt iets als dit:

code:

SELECT ding
  FROM tabel
 WHERE eigenschap IN ("5", "6", "7", "8")
GROUP BY ding
HAVING COUNT(ding) = 4;

Misschien moet je COUNT(ding) in je SELECT opnemen...

[ Voor 62% gewijzigd door Dido op 20-04-2004 13:38 ]

[curry684]

florisje schreef op 20 april 2004 @ 13:31:
ik kom er echt absoluut niet uit dus ik roep jullie hulp maar in.

Lekkere instelling maar okee...

Kijk hier eens naar:

SQL:

select ding from dingen 
    where eigenschap in (5, 6) 
    group by ding 
    having count(eigenschap) = 2;

Volgens SQL Server query analyzer is deze variant 2 keer sneller dan Dido's Magic word: don't use subqueries unless you have to

edit:
damn you Dido, bijediten terwijl ik bezig ben

[ Voor 7% gewijzigd door curry684 op 20-04-2004 13:47 ]

[Dido]

curry684 schreef op 20 april 2004 @ 13:46:
Volgens SQL Server query analyzer is deze variant 2 keer sneller dan Dido's Magic word: don't use subqueries unless you have to

Daarom gaf ik daarna een meer generieke variant

edit:
damn you Dido, bijediten terwijl ik bezig ben

Moet jij zeggen

offtopic:
Wat betreft een lekkere instelling kan ik me er bij dit soort dingen nog wel wat bij voorstellen, hoor. Ik zit mezelf ook vaak genoeg dood te staren op een uiteindelijk eenvoudig SQL probleempje. Als je een keer de verkeerde kant op redeneert is het vervloekt lastig om er weer even "fris" tegenaan te kijken. Dan kun je even een uurtje of wat wat anders gaan doen, of een collega vragen er eens naar te kijken, of een paar nerds die het leuk vinden op GoT

[ Voor 38% gewijzigd door Dido op 20-04-2004 13:52 ]

[curry684]

Zal ik dan nog maar een extra versie doen:

SQL:

select A.ding from dingen A 
    cross join dingen B 
    where A.eigenschap = 5 and B.eigenschap = 6 and a.ding = b.ding;

Deze wordt door SQL Server's query optimizer hetzelfde uitgevoerd overigens als de versie met de subquery... slimme software

[Dido]

Maar die is niet (eenvoudig) generiek te maken. Hij is wel mooi trouwens. (En bij vier eigenschapen die je afstest de nachtmerrie van de dba, maar dat mag wel eens, toch?)

[curry684]

Dido schreef op 20 april 2004 @ 13:54:
Maar die is niet (eenvoudig) generiek te maken. Hij is wel mooi trouwens. (En bij vier eigenschapen die je afstest de nachtmerrie van de dba, maar dat mag wel eens, toch?)

Wat, gaan DBA's huilen bij 4 carthetische produkten in 1 query?

[florisje]

dat met die counts werkt inderdaad goed, ik loop alleen nu weer tegen een ander probleem aan want ik moet ook nog dit kunnen doen:

ik wil alle 'dingen' die de eigenschappen (5 OF 6) EN (8 OF 9) hebben.

ik ga ondertussen even het cross join ding bekijken.

[Dido]

curry684 schreef op 20 april 2004 @ 13:55:
Wat, gaan DBA's huilen bij 4 carthetische produkten in 1 query?

Als het een flinke tabel is en als je het in een online transactie zet wel. Als het gaat om batch ken ik er eentje die ze zelf nog wel eens schreef
Querietjes met een doorlooptijd van 30 minuten... weet iemand waarom het ordersysteem zo traag is?

[whoami]

Dido schreef op 20 april 2004 @ 13:54:
Maar die is niet (eenvoudig) generiek te maken. Hij is wel mooi trouwens. (En bij vier eigenschapen die je afstest de nachtmerrie van de dba, maar dat mag wel eens, toch?)

Ben je zeker dat je daar een cartesisch produkt verkrijgt? In de WHERE clause ligt er nl. een 'link' tussen tabel A en tabel B

[Dido]

florisje schreef op 20 april 2004 @ 13:56:
dat met die counts werkt inderdaad goed, ik loop alleen nu weer tegen een ander probleem aan want ik moet ook nog dit kunnen doen:

ik wil alle 'dingen' die de eigenschappen (5 OF 6) EN (8 OF 9) hebben.

Je lijkt wel een gebruiker
Werkt het, komen er nieuwe specs

ik ga ondertussen even het cross join ding bekijken.

Die zou ik sowieso maar links laten liggen

Een oplossing zou kunnen zijn om te kijken naar (5 EN 8 ) OF (5 EN 9) OF (6 EN 8 ) OF (6 EN 9) maar dat rijst de pan uit met meer eigenschappen...

[ Voor 1% gewijzigd door Dido op 20-04-2004 14:02 . Reden: stomme 8) ! ]

[Dido]

whoami schreef op 20 april 2004 @ 13:58:
Ben je zeker dat je daar een cartesisch produkt verkrijgt? In de WHERE clause ligt er nl. een 'link' tussen tabel A en tabel B

Nou je het zegt... ik ging meer af op Curry's statement dat ie op dezelfde manier als de subquery uitgevoerd ging worden, en dat je dus vier subqueries krijgt...
Da's ook niet fijn, maar minder erg dan een cartesisch product.

[curry684]

whoami schreef op 20 april 2004 @ 13:58:
[...]


Ben je zeker dat je daar een cartesisch produkt verkrijgt? In de WHERE clause ligt er nl. een 'link' tussen tabel A en tabel B

SQL Server niet iig, die optimaliseert 'm net als de subquery variant weg tot 2 table scans en en een inner join idd. Ik zou dit echter niet op MySQL proberen

Voor TS: cross joins lekker negeren, die gaf ik alleen voor de stoerheid

[ Voor 3% gewijzigd door curry684 op 20-04-2004 14:03 ]

[florisje]

Je lijkt wel een gebruiker
Werkt het, komen er nieuwe specs

schaam!!!

ik ben bang dat het dan toch maar met php opgelost moet worden.
uit principe wou ik het echter met een query doen maar ik denk dat dat te gek wordt.
een collega heeft het net over draaitabellen/pivotdingen die idd ook nuttig klinken.

bedankt voor de hulp!

[Dido]

Eerste opzetje voor je nieuwe vraag:

code:

SELECT ding
  FROM tabel
 WHERE eigenschap IN ("5", "6")
    OR eigenschap IN ("8", "9")
GROUP BY ding
HAVING COUNT(ding) = 2;

Het probleem is nu dat je degenen met eigenschappen 5 en 6, maar zonder 8 of 9 ook krijgt.

Werkt dit?

code:

SELECT ding, eigenschap as E1, eigenschap as E2
  FROM tabel
 WHERE E1 IN ("5", "6")
    OR E2 IN ("8", "9")
GROUP BY ding, E1, E2
HAVING COUNT(E1) = 1
   AND COUNT(E2) = 1;

[ Voor 29% gewijzigd door Dido op 20-04-2004 14:09 ]

[curry684]

Dido schreef op 20 april 2004 @ 14:02:
[...]

Nou je het zegt... ik ging meer af op Curry's statement dat ie op dezelfde manier als de subquery uitgevoerd ging worden, en dat je dus vier subqueries krijgt...

Jij onderschat SQL Server

Klik!

[whoami]

Idd, dat vertaalt gewoon naar :

code:

WHERE eigenschap IN (5, 6, 8, 9)

denk ik.

[Dido]

Er zit nog wel iets fout in mijn aanvulling... E1 en E2 zijn geen aggregate functions, maar volgens mij wil ik er ook niet op groupen... damn.

SUM(eigenschap) as E1 en SUM(eigenschap) as E2 dan?

[Dido]

curry684 schreef op 20 april 2004 @ 14:07:
Jij onderschat SQL Server

Nee hoor, ik wordt alleen steeds blijer. (Ben noob op dit gebied, maar ik ben sinds kort met VS.NET aan het froebelen. Via VS.NET het ik SQL Server geinstalleerd, onder het mom dat het toch gratis was, en het bevalt me steeds beter )

[whoami]

Dido schreef op 20 april 2004 @ 14:11:
Er zit nog wel iets fout in mijn aanvulling... E1 en E2 zijn geen aggregate functions, maar volgens mij wil ik er ook niet op groupen... damn.

SUM(eigenschap) as E1 en SUM(eigenschap) as E2 dan?

Ik begrijp niet goed wat je bedoeld ?

[Dido]

whoami schreef op 20 april 2004 @ 14:15:
Ik begrijp niet goed wat je bedoeld ?

In deze:

code:

SELECT ding, eigenschap as E1, eigenschap as E2
  FROM tabel
 WHERE E1 IN ("5", "6")
    OR E2 IN ("8", "9")
GROUP BY ding, E1, E2
HAVING COUNT(E1) = 1
   AND COUNT(E2) = 1;

zou ik E1 en E2 in de group by moeten zetten (hoewel MySQL het misschien niet afdwingt )
Maar als ik dat doe krijg ik niet het gewenste resultaat:

code:

ding E1 E2
  1  5  8
  1  6  9

Zou niet opgenomen moeten worden.
Met de sum(eigenschap) as E1 en idem voor E2 zou die werken:

code:

ding E1 E2
  1  11  17

Wordt niet opgenomen omdat de count(E1)=2

Echter, door de sum werkt mijn E1 IN (5, 6) niet meer!

[ Voor 24% gewijzigd door Dido op 20-04-2004 14:24 ]

[whoami]

Waarom select je ze, als het toch maar dummies zijn?

[Dido]

Om er apart op te kunnen selecten. Dat gaat in dit geval echter niet helemaal goed, geloof ik.

* Dido gaat even van scratch beginnen

[Dido]

code:

SELECT ding
      ,SUM(IIF(eigenschap=5 OR eigenschap=6, 1, 0)) as E1
      ,SUM(IIF(eigenschap=8 OR eigenschap=9, 1, 0)) as E2
  FROM tabel
GROUP BY ding
HAVING E1 = 1
   AND E2 = 1;

Erg lelijk, vind ik zelf, maar zou het werken?
En moeten de voorwaarden hier in de HAVING of in een WHERE?

[florisje]

dido, wij komen hier net tot ongeveer dezelfde conclusie.
we hebben ongeveer eenzelfde query maar dan met case ipv iifs (die ik nog niet kon)

[Dido]

Da;s vrijwel equivalent inderdaad. Voordeel van CASE is dat het generieker is: je kunt eenvoudiger op meer eigenschappen testen. Maar werken doet het wel, denk ik

[Soultaker]

curry684 schreef op 20 april 2004 @ 14:07:
Jij onderschat SQL Server

Klik!

Ik krijg anders een ander diagrammetje hoor:

Hierin is de variant met de subselect sneller dan de variant met de cross join (hoewel het selecteren en dan tellen toch het snelste is).

Overigens heb ik de tabel gevuld met die paar rijen van de topicstarter en met een primary key constraint op id. Eigenlijk vind ik die id niet zo op z'n plaats; de eigenschappen-tabel koppelt eigenschappen aan 'dingen' en zou wat mij betreft dus precies die twee kolommen mogen hebben (die tegelijkertijd de primary key vormen).

Een primary key op twee kolommen geeft je bovendien direct een index op de eerste kolom (bv. 'ding'); misschien kan het ook uit om op de tweede kolom ('eigenschap') een index te leggen, maar dat weet ik niet zeker.

edit:
Het ging trouwens over MySQL, zie ik, dus MS-SQL specifieke oplossingen zijn niet echt interessant.

[ Voor 8% gewijzigd door Soultaker op 20-04-2004 14:43 ]

[whoami]

Dido schreef op 20 april 2004 @ 14:36:

code:

SELECT ding
      ,SUM(IIF(eigenschap=5 OR eigenschap=6, 1, 0)) as E1
      ,SUM(IIF(eigenschap=8 OR eigenschap=9, 1, 0)) as E2
  FROM tabel
GROUP BY ding
HAVING E1 = 1
   AND E2 = 1;

Erg lelijk, vind ik zelf, maar zou het werken?
En moeten de voorwaarden hier in de HAVING of in een WHERE?

Dat hangt er vanaf; als je wilt filteren op group niveau zoals jij met E1 en E2, dan moet het in de having.
Wil je op row-niveau filteren, dan moet je de where gebruiken.

[florisje]

met uw aller hulp heb ik dit opgesteld:

code:

SELECT ding,
SUM(CASE WHEN eigenschap=5 THEN 1 ELSE 0 END) +
SUM(CASE WHEN eigenschap=6 THEN 1 ELSE 0 END) AS m,
SUM(CASE WHEN eigenschap=8 THEN 1 ELSE 0 END) +
SUM(CASE WHEN eigenschap=9 THEN 1 ELSE 0 END) AS n
FROM tabel
GROUP BY ding
HAVING m>0 AND n>0

SUM(CASE WHEN eigenschap=8 THEN 1 ELSE 0 END) +
kan er eventueel nog uit als ik dus ( 5 EN 6 ) OF ( 8 ) wil hebben

bedankt iedereen, ik ben eruit

[ Voor 4% gewijzigd door florisje op 20-04-2004 15:01 ]

[curry684]

Soultaker schreef op 20 april 2004 @ 14:41:
[...]
Een primary key op twee kolommen geeft je bovendien direct een index op de eerste kolom (bv. 'ding'); misschien kan het ook uit om op de tweede kolom ('eigenschap') een index te leggen, maar dat weet ik niet zeker.

edit:
Het ging trouwens over MySQL, zie ik, dus MS-SQL specifieke oplossingen zijn niet echt interessant.

Ik had dan ook expres geen indexen aangemaakt om MySQL zo goed mogelijk te emuleren (dikke knipoog ) maar dat is idd het verschil

Dat een MSSQL oplossing niet zo interessant is neemt niet weg dat de query analyzer hoe dan ook wonderen doet voor 'de big picture' van de performance van je query.

[Dido]

whoami schreef op 20 april 2004 @ 14:42:
Dat hangt er vanaf; als je wilt filteren op group niveau zoals jij met E1 en E2, dan moet het in de having.
Wil je op row-niveau filteren, dan moet je de where gebruiken.

Ja, natuurlijk. Ik zat ff met iets in mijn hoofd dat HAVING alleen voor aggregates gebruikt kon worden. Waarom weet ik niet

florisje schreef op 20 april 2004 @ 15:00:

code:

SELECT ding,
SUM(CASE WHEN eigenschap=5 THEN 1 ELSE 0 END) +
SUM(CASE WHEN eigenschap=6 THEN 1 ELSE 0 END) AS m,
SUM(CASE WHEN eigenschap=8 THEN 1 ELSE 0 END) +
SUM(CASE WHEN eigenschap=9 THEN 1 ELSE 0 END) AS n
FROM tabel
GROUP BY ding
HAVING m>0 AND n>0

Hij kan zelfs nog iets mooier

code:

  SELECT ding
        ,SUM(CASE eigenschap
             WHEN 5 THEN 1
             WHEN 6 THEN 1
                    ELSE 0 
                       END) AS m
        ,SUM(CASE eigenschap
             WHEN 8 THEN 1
             WHEN 9 THEN 1
                    ELSE 0
                       END) AS n
    FROM tabel
GROUP BY ding
  HAVING m > 0
     AND n > 0;

Heb je een sum en een case per eigenschap minder. (Opmaak is een kwestie van smaak)

[florisje]

Hij kan zelfs nog iets mooier ...

dat gaat hem worden, is nog iets makkelijker aan te maken aan de hand van de user input (zoektermen)

[Dido]

Dat was mijn vermoeden ook