[MSSQL] En filter met join

[jelmervos]

Ik heb de volgende SQL query:

code:

SELECT
  Parent.Id, Child.Code
FROM
  Parent INNER JOIN
    Child ON Parent.Id = Child.ParentId

Welke als voorbeeld dit resultaat geeft:

code:

Id Code
1  A
1  C
2  A
2  B
2  C
3  B
3  C
4  C

Nu wil ik alle Id's waar bijvoorbeeld code B én C bijzitten. In bovenstaand voorbeeld dus alleen Id 2 en 3.

Weet iemand hoe ik dit moet oplossen binnen een MSSQL query? Alvast bedankt.

[sanderdam]

WHERE Child.Code ='B' AND 'C'

[Creepy]

Heb je zelf al iets geprobeerd? Wat lukte daar niet mee? Al gedacht aan een count i.c.m. een having?
@sanderdam: misschien eerst even testen voordat je iets roept, dit gaat niet werken

[ Voor 30% gewijzigd door Creepy op 22-03-2010 16:53 ]

[Nic]

ik denk dat een join van tweemaal de child-tabel het efficientste is in dit geval.

code:

select b.parentid from child b inner join child c on b.parentid=c.parentid
where b.code='B' and c.code='C'

[jelmervos]

dsltv schreef op maandag 22 maart 2010 @ 16:58:
ik denk dat een join van tweemaal de child-tabel het efficientste is in dit geval.

code:

select b.parentid from child b inner join child c on b.parentid=c.parentid
where b.code='B' and c.code='C'

Dat zou werken, maar wordt dat niet ontzettend traag als ik op 100 of zelfs 1000 code's wil filteren?

[HMS]

code:

SELECT
  Parent.Id, Child.Code
FROM
  Parent INNER JOIN
    Child ON Parent.Id = Child.ParentId
WHERE Child.Code == 'B'
OR WHERE Child.Code == 'C'

Lijkt mij? Heb al tijden geen SQL meer geschreven, dus correct me if I'm wrong .

[Orion84]

HMS schreef op maandag 22 maart 2010 @ 17:01:

code:

SELECT
  Parent.Id, Child.Code
FROM
  Parent INNER JOIN
    Child ON Parent.Id = Child.ParentId
WHERE Child.Code == 'B'
OR WHERE Child.Code == 'C'

Lijkt mij? Heb al tijden geen SQL meer geschreven, dus correct me if I'm wrong .

Hij is op zoek naar de Parents die zowel een child met code B als een child met code C hebben, dat is net ff wat anders dan hetgeen jij schetst

[Reinier]

SQL:

select distinct parent.id
[...]
where child.code in ( 'b' , 'c' )

Dan kom je een heel eind denk ik

Edit:
Maar niet ver genoeg

[ Voor 12% gewijzigd door Reinier op 22-03-2010 17:05 ]

[HMS]

Nou, dan een DISTINCT(Parent.Id) oid erover heen en dan is het effect er wel .

[Reinier]

HMS schreef op maandag 22 maart 2010 @ 17:05:
Nou, dan een DISTINCT(Parent.Id) oid erover heen en dan is het effect er wel .

Die distinct lijkt me wel nodig ja, maar mijn voorbeeld geeft echt niet het goede resultaat
Twee keer die child-tabel joinen (met een inner join) is m.i. de oplossing.

Zoiets:

SQL:

select distinct parent.id
from parent
inner join child as c1
on parent.id = c1.parentid
inner join child as c2
on parent.id = c2.parentid
where c1.code in ( 'b' , 'c' )
and c2.code in ( 'b' , 'c' )
and c1.code <> c2.code

[ Voor 26% gewijzigd door Reinier op 22-03-2010 17:09 ]

[Orion84]

HMS schreef op maandag 22 maart 2010 @ 17:05:
Nou, dan een DISTINCT(Parent.Id) oid erover heen en dan is het effect er wel .

Nee, want dan krijg je nog steeds allerlei Parents die alleen een B-child of alleen een C-Child hebben.

[HMS]

Orion84 schreef op maandag 22 maart 2010 @ 17:07:
[...]

Nee, want dan krijg je nog steeds allerlei Parents die alleen een B-child of alleen een C-Child hebben.

Ah op die manier. Ok.

Weet zo niet meer de discussie met JOIN vs Subqueries, maar ik zou voor zoiets gaan.

code:

SELECT DISTINCT Parent.Id
FROM Parent
WHERE Parent.Id IN (
  SELECT Child.ParentId
  FROM Child
  WHERE Child.Code == 'A')
AND WHERE Parent.Id IN (
  SELECT Child.ParentId
  FROM Child
  WHERE Child.Code == 'B')

Voetnoot: Ik doe al tijden geen SQL meer, NHibernate ftw .

[d00d]

Ik denk dat je dit wilt:

select c.ParentID from Child c where c.Code = 'B'
intersect
select c.ParentID from Child c where c.Code = 'C'

[pedorus]

jelmervos schreef op maandag 22 maart 2010 @ 17:00:
[...]

Dat zou werken, maar wordt dat niet ontzettend traag als ik op 100 of zelfs 1000 code's wil filteren?

Nee, want een join gaat zeer snel als de database de juiste indices heeft. Maar test het anders gewoon uit.
Of bedoel je bij 100'en codes om op te zoeken? Gebruik dan anders iets als:

SQL:

SELECT
  Parent.Id
FROM
  Parent INNER JOIN
    Child ON Parent.Id = Child.ParentId
WHERE
  Child.Code IN ('B','C',...)
GROUP BY
  Parent.Id
HAVING
  COUNT(*) = 100

[jelmervos]

Bedankt voor de hulp allemaal, mijn SQL is niet meer wat het geweest is. :-)
Ik ga het morgen even testen.

[Nic]

De oplossing van Pedorus is ook mooi, als je veel criteria tegelijk wilt checken (of als je bv een parentid zoekt waar minstens 90 van je 100 criteria aanwezig moet zijn)
Met subqueries zou ik niet gaan werken (zeker niet als je een parentid zoekt waar 100 child id's in voor moeten komen). Die van mij hierboven is mooi als je 2 (of 3 of 4, in die orde van grootte) wilt checken. Voor meer (en met meer flexibiliteit) zou ik die van Pedorus nemen.

Maar hij kan nog iets korter, want die parent tabel heb je helemaal niet nodig als je alleen parent id's zoekt:

code:

SELECT 
  ParentId 
FROM 
    Child 
WHERE 
  Child.Code IN ('B','C',...) 
GROUP BY 
  ParentId 
HAVING 
  COUNT(*) = <aantal criteria>

[jelmervos]

De query met HAVING lijkt het goed te doen, maar wat ik vergeten ben te vertellen in mijn start post is dat er per Id veel dezelfde codes voor kunnen komen.

De query met HAVING gaat dus verkeerd als ik alles wil met A EN B uitgaande van deze voorbeeld data:

code:

Id Code
1  A
1  A

2  A
2  B

Ik krijg nu dus Id 1 en 2, terwijl 1 helemaal geen B heeft en ik daar wel op wil filteren.

Ik heb ook de query van Reiner geprobeerd, maar die geeft helemaal geen result.

[pedorus]

Dan doe je HAVING COUNT(DISTINCT Child.Code)=100 of toch die eerste join.

[Reinier]

jelmervos schreef op dinsdag 23 maart 2010 @ 11:03:
De query met HAVING lijkt het goed te doen, maar wat ik vergeten ben te vertellen in mijn start post is dat er per Id veel dezelfde codes voor kunnen komen.

De query met HAVING gaat dus verkeerd als ik alles wil met A EN B uitgaande van deze voorbeeld data:

code:

Id Code
1  A
1  A

2  A
2  B

Ik krijg nu dus Id 1 en 2, terwijl 1 helemaal geen B heeft en ik daar wel op wil filteren.

En met een having count( distinct * )?

Ik heb ook de query van Reiner geprobeerd, maar die geeft helemaal geen result.

Dan snap ik niet hoe je parent en child tabellen er verder uitizen, ik heb het hier nagebootst en het werkt precies zoals ik bedoel