SQL: Koppeltabel omzetten naar matrix

[sariel]

Hallo iedereen,

ik heb op het ogenblik drie dergelijke tabellen:

Tabel A:
A.id, A.naam, A.info
A1,naamA1,infoA1
A2,naamA2,infoA2


Tabel B:
B.id, B.naam, B.info
B1,naamB1,infoB1
B2,naamB2,infoB2


Tabel AB (koppeltabel):
AB.id, AB.A_id, AB.B_id

1,A1,B1
2,A1,B2
3,A2,B1

Nu wil ik een dergelijke matrix er uit krijgen:

     A1 A2
B1    1  1
B2    1  0

Heeft iemand tips hoe ik dit voor elkaar kan krijgen? Ik weet dat die met PHP code gemakkelijk te doen is maar ik wil de database niet belasten met 500+ queries als het sneller en redelijk gemakkelijk kan met 1 of 2 queries.

JF

[RobIII]

Waarom zou dat 500+ queries kosten

[Foeijonghaai]

sariel schreef op zondag 17 februari 2013 @ 22:03:
Hallo iedereen,

ik heb op het ogenblik drie dergelijke tabellen:

Tabel A:
A.id, A.naam, A.info
A1,naamA1,infoA1
A2,naamA2,infoA2


Tabel B:
B.id, B.naam, B.info
B1,naamB1,infoB1
B2,naamB2,infoB2


Tabel AB (koppeltabel):
AB.id, AB.A_id, AB.B_id

1,A1,B1
2,A1,B2
3,A2,B1

Nu wil ik een dergelijke matrix er uit krijgen:

     A1 A2
B1    1  1
B2    1  0

Heeft iemand tips hoe ik dit voor elkaar kan krijgen? Ik weet dat die met PHP code gemakkelijk te doen is maar ik wil de database niet belasten met 500+ queries als het sneller en redelijk gemakkelijk kan met 1 of 2 queries.

JF

Wil je er echt een matrix uit krijgen of wil je gewoon alle mogelijke combinaties van A en B weten en of ze in je koppeltabel voorkomen?

[sariel]

Het liefst heb ik iets van een matrix zodat ik dat gemakkelijk om kan zetten naar een matrix van checkboxes voor een website waarin aangegeven staat welke combinaties wel en niet bestaan.

RobIII schreef op zondag 17 februari 2013 @ 22:26:
Waarom zou dat 500+ queries kosten

omdat het mogelijk is dat ik zo veel rijen uit B krijg waar ik voor iedere rij een query zal moeten uitvoeren.

[ Voor 41% gewijzigd door sariel op 18-02-2013 10:05 ]

[Grijze Vos]

je kunt toch joinen?

[Verwijderd]

Je wilt in zulke gevallen een resultaat krijgen dat alle cellen in de matrix bevat, maar niet zozeer als rijen/kolommen in je resultset. Wat je het best kunt doen is met één query alle waarden opvragen en ze met PHP code in een matrix zetten.

Wil je ook "lege" cellen opvragen, gebruik dan LEFT JOINs, anders INNER JOINs.

[Foeijonghaai]

sariel schreef op maandag 18 februari 2013 @ 10:03:
Het liefst heb ik iets van een matrix zodat ik dat gemakkelijk om kan zetten naar een matrix van checkboxes voor een website waarin aangegeven staat welke combinaties wel en niet bestaan.

Dat lijkt me een presentatieprobleem en geen data-probleem. Ik zou het dan ook oplossen in je weblaag.

omdat het mogelijk is dat ik zo veel rijen uit B krijg waar ik voor iedere rij een query zal moeten uitvoeren.

Alle data die je nodig heb kan je eruit krijgen met 1 query.

[sariel]

Verwijderd schreef op maandag 18 februari 2013 @ 10:06:
Je wilt in zulke gevallen een resultaat krijgen dat alle cellen in de matrix bevat, maar niet zozeer als rijen/kolommen in je resultset. Wat je het best kunt doen is met één query alle waarden opvragen en ze met PHP code in een matrix zetten.

Wil je ook "lege" cellen opvragen, gebruik dan LEFT JOINs, anders INNER JOINs.

Bedankt voor de tip. Ik zal er mee aan de slag. Ik hoop dat het me lukt, ben wat roestig met joins en zo (ik programmeer niet dagelijks...)

[Verwijderd]

Om niet al te lullig te doen toch nog een hint:

Je wilt voor elke combinatie van A en B iets hebben. Dat impliceert een CROSS JOIN.
Vervolgens wil je uit de koppeltabel informatie hebben. Afhankelijk van of je lege cellen wel of niet wilt hebben in je resultaat, gebruik je hier een LEFT JOIN of een INNER JOIN.

SQL:

SELECT
   *
FROM
   A CROSS JOIN B
   LEFT JOIN AB ON (AB.A_id = A.id AND AB.B_id = B.id)

[markjans83]

Je gebruikt een JOIN vooral om snel (qua performance) een selectie van data op te halen vanuit je DB naar je applicatie. In jouw geval heb je altijd alle data nodig. Althans, niet alle kolommen, maar wel alle rijen. Ik zou dus gewoon 3 queries (SELECT id, naam FROM A; SELECT id, naam FROM B, SELECT * FROM AB) uitvoeren en in je applicatie de matrix opbouwen:

code:

ab = new two_dimensional_array();
for-each AB {
  ab[A.id][B.id] = 1;
}
print "\t";
for-each B {
  print B.naam, "\t";
}
for-each A {
  print "\n", A.naam, "\t";
  for-each B {
    print_checkbox_with_value(ab[A.id][B.id] != null ? "1" : "0");
  }
}

@Cheatah, volgens de MySQL documentatie is een CROSS JOIN precies hetzelfde als een INNER JOIN. Ik zou jouw query zelf eerder als volgt schrijven:

SQL:

SELECT A.naam, B.naam
  FROM AB
 INNER JOIN A ON AB.A_id = A.id
 INNER JOIN B ON AB.B_id = B.id.

Je hebt geen LEFT JOIN nodig, want ik mag wel aannemen dat in de koppeltabel geen id's staan die niet in A of B staan.

Het resultaat van deze laatste query geeft geen matrix terug, maar een tabel van 2 namen en dat zal dan nog steeds door de applicatie omgezet moeten worden in een matrix. In dat geval denk ik dat 3 queries handiger zijn, want dan hoef je de kopjes/linker kolom (de namen) niet uit het resultaat te vissen, maar heb je die direct tot je beschikking.

[martennis]

De enige manier waarop dit kan is met een pivot. Nadeel is alleen dat je expliciet voor elke id in kolom A ook een kolom moet opgeven in je query. Als je erg veel gegevens voor 1 van beide tabellen hebt, dan is waarschijnlijk programmeren met alleen een join makkelijker en flexibeler.

SQL:

SELECT [1] AS A1, [2] AS A2, [3] AS A3
FROM AB
INNER JOIN A ON A.Aid = AB.idA
PIVOT
(
    COUNT(AB.idB) FOR AB.idA IN ([1], [2], [3])
) AS pvt

Ohja, dit is voor MSSQL, ik weet niet of MySQL de 'PIVOT' functie ondersteund.

[Verwijderd]

markjans83 schreef op maandag 18 februari 2013 @ 15:08:

@Cheatah, volgens de MySQL documentatie is een CROSS JOIN precies hetzelfde als een INNER JOIN. Ik zou jouw query zelf eerder als volgt schrijven:

SQL:

Query die iets heel anders doet

Als ik mijn ervaring raadpleeg zie ik anders onmiddelijk dat jouw query onmogelijk meer resultaten zal teruggeven dan in de koppeltabel staan, en dat mijn query altijd een resultaat van A x B rijen heeft. Zeg dan gewoon duidelijk dat je een andere methode beter vindt, in plaats van te doen alsof je een equivalente query hebt. Voor mensen met weinig ervaring zal dat namelijk totaal niet duidelijk zijn.

Dit is overigens nu net een van de heel weinige situaties waarin ik toch die cross join zou aanraden, al was het omdat je ze anders helemaal nooit zou gebruiken.

Of het in de ingewanden van een implementatie gelijk is aan andere joins boeit me eigenlijk niets. Ik zou niet uitgaan van optimalisaties (or whatever the reason may be) van een DBMS, zeker omdat je er zelf bij hebt verzonnen dat het om MySQL gaat. Wat mij betreft gaat het om het principe, en kunnen we implementaties en optimalisaties het best nog even buiten beschouwing laten.