[SQL]Selectie op meerdere onderdelen

[Jaspertje]

Hmm, de titel is niet echt super, maar het probleem wel

Ik heb de volgende drie tabellen:
Coverages
coverageid
name

id	naam
1	Overlijden box 1
2	Overlijden box 3
3	AO box 1
4	AO box 3

Product_Coverages
coveragecombinationid
productid

Coveragecombinationid	productid
1	1
2	1
3	2
4	3

CoverageCombinations
coveragecombinationid
coverageid

coveragecombinationid	coverageid
1	1
1	3
2	1
3	4
4	4

Een product heeft dus een aantal coveragecombinations. Die weer bestaan uit een of meerdere coverages. In bovenstaande voorbeeld heeft dus "product 1" twee coveragecombinationid's (1 en 2) Dat betekend dat dit product gekozen mag worden als er een tekort is in (Overlijden box 1 en AO box 1) of (Overlijden box 1) Maar dus niet als er alleen een AO box 1 tekort is. Dat laatste is het probleem want ik krijg het met joins niet voor elkaar om alleen de producten te tonen die wel afgesloten mogen worden als je alleen een AO box 1 tekort hebt

Onderstaande is een voorbeeld van hoe het niet werkt. Nu krijg je alle producten te zien die ergens in een coveragecombination AO box 1 hebben zitten. Maar je haalt niet degene eruit die alleen maar dat hebben.

SQL:

select p.productid from products p 
inner join product_coverages pcv on p.productid = pcv.productid
inner join coverages_combinations pc on pcv.coveragecombinationid = pc.coveragecombinationid
inner join coverages c on pc.coverageid = c.coverageid
where c.coverageid = 3

Is het mogelijk om dit in SQL voor elkaar te krijgen, of kan ik dit beter oplossen in code (dan gaat het wel lukken, maar ik vind het leuker/mooier in SQL )

[pjotrk]

Met alleen joins zal dat denk ik inderdaad niet lukken, maar je kan natuurlijk wel met twee subselects bepalen dat een product wel AO box 1 heeft, maar niet een van alle andere.

Dan krijg je dus ongeveer (niet getest):

SQL:

select p.productid 
from products p
where p.producid 
  in (select pc.productid 
      from Product_Coverages pc 
        inner join CoverageCombinations cc on pc.Coveragecombinationid = cc.Coveragecombinationid
        inner join coverages c on c.id = cc.coverageid 
      where c.id = 3)
and not 
  in (select pc.productid 
      from Product_Coverages pc 
        inner join CoverageCombinations cc on pc.Coveragecombinationid = cc.Coveragecombinationid
        inner join coverages c on c.id = cc.coverageid 
      where c.id <> 3)      

[Creepy]

Tikje door naar PRG.

[pedorus]

Jaspertje schreef op maandag 11 augustus 2008 @ 14:18:
Is het mogelijk om dit in SQL voor elkaar te krijgen, of kan ik dit beter oplossen in code (dan gaat het wel lukken, maar ik vind het leuker/mooier in SQL )

Ik zou dit gewoon in SQL oplossen, dat scheelt de overhead van het transporteren van onnodige gegevens en het implementeren van extra logica. Er zijn vele oplossingen mogelijk:

• Je eigen query met een left join uitbreiden op coverages c2 met conditie c2.id<>3 in de on-clause en checken op c2.id is null
• Een group by met having min(c.coverageid)=3 and count(c.coverageid)=1. Hierbij moet de combinatie ook in de groep zitten. Bij Mysql kan de min() weg.
• een view op coveragecombinations met de count er in, en daarmee joinen met count=1 als conditie. Dit is het makkelijkst uit te breiden naar meer producten denk ik zo.
• De oplossing van pjortk aanpassen totdat ie klopt (let op bij producten met meer dan een coveragecombination)
• etc.

[pjotrk]

Ik zie inderdaad dat ik een belangrijk detail over het hoofd heb gezien. Als ik het goed lees mag een product pas gekozen worden als er precies aan een bijbehorende coveragecombination wordt voldaan?

Dan is het volgende wellicht het makkelijkst. Dit is makkelijk voor iedere coveragecombinatie te genereren en werkt ook voor een tekort in meer dan één coverage (als het echter alleen gaat om de producten die beschikbaar zijn met slechts een tekort in één coverage, dan zijn de opties van pedorus, natuurlijk eenvoudiger voor jouw probleem):

Wanneer je wilt weten welke producten afgenomen mogen worden bij alleen een AO Box 1 tekort krijg je de volgende query.

SQL:

Select pc.productid 
from Product_Coverages pc
where exists 
  (select count(CoverageCombinationId)
   from CoverageCombinations
   where CoverageCombinationId = pc.CoverageCombinationId and coverageid in (3)
   having count(CoverageCombinationId)=1)
and CoverageCombinationId not in 
  (select CoverageCombinationId
   from   CoverageCombinations
   where not(coverageid in (3))) 

Wanneer je wilt weten welke producten afgenomen mogen worden bij een Overlijden box 1, Overlijden box 3 en AO Box 1 tekort krijg je de volgende query.

SQL:

Select pc.productid 
from Product_Coverages pc
where exists 
  (select count(CoverageCombinationId)
   from CoverageCombinations
   where CoverageCombinationId = pc.CoverageCombinationId and coverageid in (1,2,3)
   having count(CoverageCombinationId)=3)
and CoverageCombinationId not in 
  (select CoverageCombinationId
   from   CoverageCombinations
   where not(coverageid in (1,2,3))) 

Er zijn ook in deze trend nog vele andere oplossingen te bedenken, zoals:

SQL:

Select pc.productid 
from Product_Coverages pc
where CoverageCombinationId in (select CoverageCombinationId from CoverageCombinations where coverageid = 1)
  and CoverageCombinationId in (select CoverageCombinationId from CoverageCombinations where coverageid = 2)
  and CoverageCombinationId in (select CoverageCombinationId from CoverageCombinations where coverageid = 3)
  and CoverageCombinationId not in (select CoverageCombinationId from CoverageCombinations where not(coverageid in (1, 2, 3))) 

Hoewel simpeler te lezen, is het weer iets omslachtiger om te genereren

[ Voor 5% gewijzigd door pjotrk op 12-08-2008 02:31 ]

[Voutloos]

Bottomline van al deze opties en alle Grote Hoe Moet Deze Query vragen: Denk in sets. Bedenk hoe je de verzameling rijen van de deelproblemen verzameld en voeg dat samen.

[Jaspertje]

In de oude code (we zijn aan het migreren) hadden we al een oplossing staan zoals bovenstaand met alle coverages los benoemd in de code, maar dat was een oplossing die ik niet mooi vind, vandaar mijn vraag. Ik ga stoeien met bovenstaande voorbeelden. Tnx