[mysql] Uitdagende join

[Blaurens]

Ik heb 1 tabel met texten, en 1 tabel met referenties naar die texten.

SQL:

Texten:
id      text
1       "Hallo"
2       "wereld"
3       "dit is een"
4       "test"

Referenties:
optie1  optie2  
1       2
3       4
1        4
2        4

Ik wil een query het volgende teruggeeft:

SQL:

output1     output2
"Hallo"     "Wereld"
"dit is een"   "test"
"Hallo"     "test"
"wereld"       "test"

Ik gebruik mysql 3.*

Ik heb al wat verwoedde pogingen gedaan, maar ik loop intern steeds vast .

Mijn laatste poging lijkt hier op:

SQL:

SELECT 
texten.text as output1,
texten.text as output2,
from texten
INNER JOIN 
referenties
ON 
output1 = texten.text where referentie.optie1=texten.id and
output2 = texten.text where referentie.optie2=texten.id

maar dat laatste stukje kan natuurlijk niet. Ik heb geprobeerd wijs te worden uit de voorbeelden van join's die in de faq stonden, maar kwam daar helaas niet uit met dit specifieke probleem.

Kan dit uberhaubt, of moet ik in php gewoon de boel maar zelf aan elkaar klussen? Het leek me eleganter en sneller om dit in mysql op te lossen. Hartelijk dank voor mogelijke hulp!

[ Voor 9% gewijzigd door Blaurens op 05-11-2004 19:09 . Reden: layout ]

[Soultaker]

Natuurlijk kan dit. Als je twee verschillende teksten-tabellen zou hebben (eentje voor de eerste helft en eentje voor de tweede helft) dan zou je waarschijnlijk geen probleem hebben. Op dezelfde manier kun je ook twee keer uit dezelfde tabel queryen:

SQL:

SELECT
  texten1.text as output1,
  texten2.text as output2
FROM
  texten AS texten1,
  texten AS texten2,
  referenties
WHERE
  referenties.optie1 = texten1.id,
  referenties.optie2 = texten2.id

[Verwijderd]

SQL:

SELECT
  texten.text as output1,
  texten.text as output2
  from texten
INNER JOIN
  referenties
ON
  referentie.optie1=texten.id,
  referentie.optie2=texten.id

[ Voor 6% gewijzigd door Verwijderd op 05-11-2004 19:13 ]

[Verwijderd]

weet niet of dit in mysql kan, maar in postgres gaf dit het gewenste resultaat:

tabel1
id | t
----+------------
1 | hallo
2 | wereld
3 | dit is een
4 | test

tabel2:
o1 | o2
----+----
1 | 2
3 | 4
1 | 4
2 | 4

SQL:

select t1.t as tekst1, n.t as tekst2 from
((t2 inner join t1 on t2.o1 = t1.id) inner join t1 as n on t2.o2 = n.id);

tekst1 | tekst2
------------+--------
hallo | wereld
hallo | test
wereld | test
dit is een | test


_{edit: left outer join vervangen door inner join}

[ Voor 13% gewijzigd door Verwijderd op 05-11-2004 19:32 ]

[jvdmeer]

Volgens mij is dit qua syntax de mooiste omdat de index optimaal gebruikt kan worden:

code:

SELECT t1.text, t2.text
  FROM referenties r
  JOIN texten t1 ON t1.id=r.optie1
  JOIN texten t2 ON t2.id=r.optie2

[Blaurens]

Soultaker: de versie die je postte, werkte niet, tot ik de laatste komma in een AND veranderde.

SQL:

SELECT
  texten1.text as output1,
  texten2.text as output2
FROM
  texten AS texten1,
  texten AS texten2,
  referenties
WHERE
  referenties.optie1 = texten1.id AND
  referenties.optie2 = texten2.id

Dit werkt perfect! Hartelijk dank! Ik ben op de een of andere wonderlijke wijze in de door mij gebruikte mysql-tutorials nog niet eerder het selecteren uit meerdere tabellen tegen gekomen, heb het kennelijk tot nu toe ook nooit nodig gehad. Ik vind het lastig te begrijpen hoe de oplossing uiteindelijk werkt, al komt dat misschien grotendeels doordat ik moe ben. Als ik het goed begrijp, onstaat er met

SQL:

FROM
  Tabel1,Tabel2,Tabel3

eigenlijk een grote nieuwe pool, die bestaat uit alle mogelijkheden van Tabel1 t/m 3 bij elkaar. Daaruit wordt vervolgens dus de gevallen gekozen waar optie1=tabel1.id en optie2=tabel2.id. Klopt dit?

WalfredGeesink: zelfde issue met de komma, alleen krijg ik een blanco resultaat. (of preciezer: phpmyadmin brouwt er niets van, want ik krijg een window met de query op de plek waar eigenlijk de resultaten moeten staan. Raar, had ik nog niet eerder gezien.

DarthRaider: Ik ken geen postgres, en (kennelijk) schiet mijn sql kennis ook duidelijk te kort, want ik kreeg het helaas niet omgevormd tot (werkende) mysql.

jvdmeer: phpmyadmin klaagt het volgende als ik je query probeer:

SQL:

SQL-query :  

SELECT t1.text, t2.text
FROM referenties r
JOIN texten t1 ON t1.id = r.optie1
JOIN texten t2 ON t2.id = r.optie2
LIMIT 0 , 30 

MySQL retourneerde: 

You have an error in your SQL syntax near 'ON t1.id = r.optie1
JOIN texten t2 ON t2.id = r.optie2 LIMIT 0, 30' at line 3

Slaap en/of gebrek aan kennis (moet nog es een goeide, heldere JOIN tutorial vinden ) verhindert mij zelf te snappen waarom dat fout gaat.

Een ieder hartelijk dank voor de snelle reacties!!

[[ash]]

En als je hem zo uitvoerd?

SQL:

SQL-query :  

SELECT t1.text, t2.text
FROM referenties AS r
JOIN texten AS t1 ON t1.id = r.optie1
JOIN texten AS t2 ON t2.id = r.optie2
LIMIT 0 , 30 

[Blaurens]

Had ik ook al geprobeerd, en werkt helaas ook niet:

SQL:

SELECT t1.text, t2.text
FROM referenties AS r
JOIN texten AS t1 ON t1.id = r.optie1
JOIN texten AS t2 ON t2.id = r.optie2
LIMIT 0 , 30 

MySQL retourneerde: 

You have an error in your SQL syntax near 'ON t1.id = r.optie1
JOIN texten AS t2 ON t2.id = r.optie2 LIMIT 0, 30' at line 3

[jvdmeer]

Zowel de versie met AS:

SQL:

SELECT t1.text, t2.text
FROM referenties AS r
JOIN texten AS t1 ON t1.id = r.optie1
JOIN texten AS t2 ON t2.id = r.optie2

als de versie zonder AS:

SQL:

SELECT t1.text, t2.text
FROM referenties AS r
JOIN texten AS t1 ON t1.id = r.optie1
JOIN texten AS t2 ON t2.id = r.optie2

werken hier:


Alleen heb ik optie1 en optie2 perongeluk opties1 en opties2 genoemd.

[Blaurens]

Heb je misschien MySQL 4.x? Ik gebruik nl. 3.x en misschien zit daar het verschil in?

[jvdmeer]

Blaurens schreef op 08 november 2004 @ 13:29:
Heb je misschien MySQL 4.x? Ik gebruik nl. 3.x en misschien zit daar het verschil in?

`
Ja, mysql 4.0.21. Maar ik wist niet dat dit niet kon 3.x

[Soultaker]

Blaurens schreef op 05 november 2004 @ 23:53:
Soultaker: de versie die je postte, werkte niet, tot ik de laatste komma in een AND veranderde.

Ah, dat klopt ook. Ik had 'm uit m'n hoofd neergeschreven en me daarin vergist.

Ik vind het lastig te begrijpen hoe de oplossing uiteindelijk werkt, al komt dat misschien grotendeels doordat ik moe ben. Als ik het goed begrijp, onstaat er met

SQL:

FROM
  Tabel1,Tabel2,Tabel3

eigenlijk een grote nieuwe pool, die bestaat uit alle mogelijkheden van Tabel1 t/m 3 bij elkaar. Daaruit wordt vervolgens dus de gevallen gekozen waar optie1=tabel1.id en optie2=tabel2.id. Klopt dit?

SQL is gebaseerd op relationele algebra. Daarbij wordt er vanuit gegaan dat als je twee verzamelingen hebt (A en B, bijvoorbeeld) AxB bestaat uit alle combinaties van waarden uit A en B. Als A = { 1, 2, 3 } en B = { x, y }, dan geldt dus AxB = { (1,x), (1,y), (2,x), (2,y), (3,x), (3,y) }.

Je kunt best een verzameling met zichzelf combineren; BxB = { (x,x), (x,y), (y,x), (y,y) }. Ook kun je je vast wel voorstellen dat het geen probleem is om alle combinaties van de elementen uit meer dan twee verzamelingen te nemen; BxAxB = { (x, 1, x), (x, 1, y), (x, 2, x) ... enzovoorts ... (y, 3, y) }. (Merk op dat dit hetzelfde is als (BxA)xB of Bx(AxB); die relatie is associatief.)

Tabellen zijn gewoon verzamelingen waarvan de waarden tupels zijn (een tupel is een paar, of een drietal, of een viertal, enzovoorts). Wat je concreet met de SQL query doet, is de verzameling texten x referenties x texten berekenen. (Die tabellen staan in de 'FROM' clausule.) Als je de 'WHERE' clausule weglaat, krijg je dan ook het resultaat zoals ik dat hierboven beschreven heb, bestaande uit een heleboel rijen van de vorm (texten1.id, texten1.text, texten2.id, texten2.text, referenties.optie1, referenties.optie2).

Nu wil je natuurlijk een bepaalde deelverzameling daarvan hebben, en wel alleen die rijen waarvoor geldt dat texten1.id = referenties.optie1, en texten2.id = referenties.optie2. Dat is precies wat er in de 'WHERE' clausule staat. Je kunt dat beschouwen alsof je uit de eerdere verzameling alle rijen wegstreept die niet voldoen aan dat predicaat. Een rij (1, "foo", 2, "bar", 3, 4) voldoet bijvoorbeeld niet; een rij (1, "foo", 2, "bar", 1, 2) wel.

Verder gebruik je hier de 'SELECT' clausule omdat slechts een paar velden uit elke rij interessant zijn; de titels. Een "SELECT *" zou de rijen zoals ik ze hier beschreef opleveren. Overigens voert MySQL de query niet daadwerkelijk uit zoals ik heb beschreven (het maken van zo'n grote nieuwe verzameling zou niet efficient zijn) maar het resultaat is wel hetzelfde als met de conceptuele beschrijving. Het is daarom meestal verstandig om van het conceptuele model uit te gaan, en daar je queries op te baseren, en de SQL server laten bedenken hoe 'ie 'm het handigste kan uitvoeren.

[ Voor 26% gewijzigd door Soultaker op 08-11-2004 23:37 ]