[SQL] n-op-m relatie, exact dezelfde relaties opzoeken

[jvdmeer]

Ik zit met een probleem, en zeer gesimplificeerd komt het op het volgende neer. Ik heb een koppeltabel voor de n-op-m relaties. En uit die tabel wil ik de items hebben die exact dezelfde relaties hebben.

Even ter verduidelijking:

code:

koppeltabel

veld1 veld2
===========
1     A
1     B
2     A
2     B
3     A
4     A
4     B
4     C

Zowel veld1 als veld2 verwijzen naar een primary key van een andere tabel. N:M volgens het boekje dus.

ik wil nu als variabele bijv invullen: 1 en dan als resultaat krijgen:

code:

result
======
1
2

1 natuurlijk, die heeft dezelfde relaties als zichzelf
2 ook, die heeft net als een een relatie met A en B

3 & 4 ontbreken, om de volgende redenen:
3 niet, want die mist de relatie met B
4 niet, want die heeft de relatie met C teveel.


De gebruikte SQL-server is MS, dus alle mogelijkheden van subquery's, selfjoin, enz... mogen gebruikt worden.

[Goodielover]

En als 3 en 4 ook dezelfde relaties, maar andere dan 1&2 zouden hebben, wat moet er dan uitkomen?

[Verwijderd]

Semantisch zijn de relaties 1-A en 2-A niet gelijk. Je 'probleem' klopt dus niet. Wat WIL je precies bereiken, want dan wordt het wellicht duidelijker, wat je nu hebt lijkt me onzin: ALS 1-A en 2-A dezelfde relatie zijn, dan kun je de relatie 1-A objectifyen (dus met een aparte key ervoor) en een constraint plaatsen middels een check constraint zodat bij het inserten van 2-A er een foutmelding op treedt (en men dus de key van 1-A moet gebruiken).

[Verwijderd]

Zoals al gezegt, je koppeltabel is niet goed opgebouwt.

Je hebt 1 relatie over verschillende records, en dan mis je dus net het doel van je koppeltabel.

[Goodielover]

Als ik de topic starter goed begrijp bedoelt hij:
er is een normale en goed gedefinieerde N:M relatie.

de vraag is dus bijvoorbeeld: (N:M tussen klant en artikel)
welke klanten hebben precies dezelfde artikelen gekocht

Opzich geen rare vraag, alleen hoe wil hij het gerepresenteerd zien is mijn vraag

[Janoz]

Waarschijnlijk wordt met variabele 1 bedoeld dat alle andere 'veld1'-en die dezelfde relatie hebben als 1 terug gegeven moeten worden _{denk ik}.. Maar misschien is het handig als de topicstarter ff uitsluitsel geeft ..

[SuperRembo]

De koppeling is uniek (neem ik aan). Dan kan je het aantal overeenkomende relaties bepalen. Als dat aantal overeen komt met het aantal relaties van je referentie, dan zijn de relaties gelijk.
Dan krijg je zoiets:

SELECT veld1
FROM koppeltabel
WHERE veld2 IN (SELECT veld2 FROM koppeltabel WHERE veld1=@par1)
GROUP BY veld1
HAVING COUNT(veld2) = (SELECT COUNT(veld2) FROM koppeltabel WHERE veld1=@par1)

[ Voor 5% gewijzigd door SuperRembo op 26-11-2002 20:51 ]

[jvdmeer]

Verwijderd schreef op 26 November 2002 @ 18:51:
Semantisch zijn de relaties 1-A en 2-A niet gelijk. Je 'probleem' klopt dus niet. Wat WIL je precies bereiken, want dan wordt het wellicht duidelijker, wat je nu hebt lijkt me onzin: ALS 1-A en 2-A dezelfde relatie zijn, dan kun je de relatie 1-A objectifyen (dus met een aparte key ervoor) en een constraint plaatsen middels een check constraint zodat bij het inserten van 2-A er een foutmelding op treedt (en men dus de key van 1-A moet gebruiken).

Er is sprake van 3 tabellen.
De eerste tabel heeft als primary key 1..x en daaraan gekoppeld een hoop extra velden.
De tweede tabel heeft als primary key A..xx en ook daaraan gekoppeld een hoop extra data.
De derde tabel (zie OP) koppelt deze twee tabellen, veld1 bevat dus de primary-key van tabel 1 en veld 2 bevat de primary-key van tabel 2.

Omdat er N:M relaties zijn, is er een koppeltabel nodig.

Om het iets concreter te maken:
Tabel 1, bevat computer-ID's en tabel 2 bevat programma-ID's. De koppeltabel vermeld welke programma's op welke pc geïnstalleerd zijn.

Als er nu een probleem optreed, bij computer 1, (met programma's A & , wil ik graag weten op welke pc's exact dezelfde programma's zijn als op de probleempc. Om structurele problemen eerder in kaart te brengen. Denk i.p.v. programma's ook aan geinstalleerde drivers e.d.

Door nu aan de (gezochte) querie als variabele pc1 op te geven, verwacht ik als antwoord: pc1 en pc2. Omdat op deze pc's exact dezelfde programma's cq. drivers zijn geïnstalleerd.

Ik heb al enkele andere querie's in gebruik, waarbij ik pc's conflicterende software kan identificeren. Dus bv. alle pc met videodriver 'x' en geluidsdriver 'y', maar ik stuit op problemen bij het definiëren van deze querie.

Hoop dat het nu duidelijker is.

[Janoz]

Ah, dus Goodielover + ik hadden het toch bij het rechte eind

[SuperRembo]

Die vorige query klopt niet, die geeft ook resultaat als er extra relaties zijn.
Ik denk dat deze wel klopt.

COUNT(k1.veld2) is het totale aantal relatie voor veld1
COUNT(k2.veld2) is het aantal overeenkomende relaties
MAX(COUNT(k2.veld2)) is het maximaal aantal overeenkomende relaties (dus ook het gezochte aantal)
COUNT(k1.veld2)=MAX(COUNT(k2.veld2)) alle relaties komen voor
COUNT(k2.veld2)=MAX(COUNT(k2.veld2)) er zijn geen extra relaties

SELECT k1.veld1
FROM koppeltabel k1
LEFT OUTER JOIN (SELECT veld2 FROM koppeltabel WHERE veld1=@veld1) k2 ON k1.veld2=k2.veld2
GROUP BY k1.veld1
HAVING (COUNT(k1.veld2)=MAX(COUNT(k2.veld2))) AND (COUNT(k2.veld2)=MAX(COUNT(k2.veld2)))

[jvdmeer]

SuperRembo schreef op 26 November 2002 @ 23:41:
SELECT k1.veld1
FROM koppeltabel k1
LEFT OUTER JOIN (SELECT veld2 FROM koppeltabel WHERE veld1=@veld1) k2 ON k1.veld2=k2.veld2
GROUP BY k1.veld1
HAVING (COUNT(k1.veld2)=MAX(COUNT(k2.veld2))) AND (COUNT(k2.veld2)=MAX(COUNT(k2.veld2)))

Met zoveel SQL ben ik de weg kwijt, en de server ook

Ik krijg hier op het stukje achter HAVING:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Tot aan HAVING wordt het goed uitgevoerd.

[SuperRembo]

Ik had geen server om te testen, MAX(COUNT(...)) mag blijkbaar niet.
Deze werkt wel (getest!)

SQL:

SELECT veld1
FROM (
    SELECT k1.veld1, COUNT(k1.veld2) AS c1, COUNT(k2.veld2) AS c2
    FROM koppeltabel k1 LEFT OUTER JOIN (
        SELECT veld2
        FROM koppeltabel
        WHERE veld1 = 1) k2 ON k1.veld2 = k2.veld2
        GROUP BY k1.veld1) k
    WHERE (c1 = c2) AND (c2 = (
        SELECT COUNT(veld2)
        FROM koppeltabel
        WHERE veld1 = 1))

[jvdmeer]

SuperRembo schreef op 27 november 2002 @ 11:03:
Ik had geen server om te testen, MAX(COUNT(...)) mag blijkbaar niet.
Deze werkt wel (getest!)

SQL:

SELECT veld1
FROM (
    SELECT k1.veld1, COUNT(k1.veld2) AS c1, COUNT(k2.veld2) AS c2
    FROM koppeltabel k1 LEFT OUTER JOIN (
        SELECT veld2
        FROM koppeltabel
        WHERE veld1 = 1) k2 ON k1.veld2 = k2.veld2
        GROUP BY k1.veld1) k
    WHERE (c1 = c2) AND (c2 = (
        SELECT COUNT(veld2)
        FROM koppeltabel
        WHERE veld1 = 1))

bedankt, deze werkt. Nu nog even ombouwen naar de werkelijke tabel, en ik kan hem gebruiken.