[MySQL] Joinen op een resultset; mogelijk?*

[Verwijderd]

Ik gebruikte eerder een oracle database maar aangezien ik nu MySQL gebruik, moet ik de queries omzetten. In Oracle kan je bijvoorbeeld meerdere tabellen in de FROM zetten en deze dan joinen via de WHERE clause. In MySQL doe je dit echter via INNER JOIN ... ON.
Dat is niet zo moeilijk maar in Oracle kan je ook een JOIN doen op een table die op zich eigenlijk een resultset van een subquery is.

Zie voorbeeld hieronder (Oracle query):
SELECT OWNER.PURCHASEDATE AS PURCHASEDATE,
U.FIRSTNAME AS B_FIRSTNAME,
U.LASTNAME AS B_LASTNAME,
EXCHANGE.HANDINDATE AS HANDINDATE,
EXCHANGE.BORROWED AS BORROWED
FROM WIKILIB_OWNER OWNER,
(SELECT *
FROM WIKILIB_EXCHANGE EX1, WIKILIB_OWNER O1
WHERE EX1.ID =
(SELECT MAX(EX2.ID)
FROM WIKILIB_EXCHANGE EX2, WIKILIB_OWNER O
WHERE EX2.ITEMCOPY = O.ID AND O.ID = O1.ID AND EX2.BORROWED IS NOT NULL)
) EXCHANGE,
WIKILIB_USER U
WHERE EXCHANGE.ITEMCOPY(+) = OWNER.ID
AND EXCHANGE.BORROWER = U.USERNAME(+)
AND OWNER.ID = ?

DIt is de MySQL query:
SELECT OWNER.PURCHASEDATE AS PURCHASEDATE,
U.FIRSTNAME AS B_FIRSTNAME,
U.LASTNAME AS B_LASTNAME,
EXCHANGE.HANDINDATE AS HANDINDATE,
EXCHANGE.BORROWED AS BORROWED
FROM WIKILIB_OWNER OWNER
LEFT JOIN (SELECT *
FROM WIKILIB_EXCHANGE EX1, WIKILIB_OWNER O1
WHERE EX1.ID =
(SELECT MAX(EX2.ID)
FROM WIKILIB_EXCHANGE EX2, WIKILIB_OWNER O
WHERE EX2.ITEMCOPY = O.ID AND O.ID = O1.ID AND EX2.BORROWED IS NOT NULL)
) EXCHANGE ON EXCHANGE.ITEMCOPY = OWNER.ID
LEFT JOIN WIKILIB_USER U ON EXCHANGE.BORROWER = U.USERNAME
WHERE OWNER.ID = ?

Het vetgedrukte gedeelte geeft echter nog problemen in MySQL. Ik krijg de melding: "Duplicate column name 'ID' ". Ik denk dat het komt doordat je niet kan joinen op een resultset maar enkel op een volledige tabel. Als ik echter join op de tabel WIKILIB_EXCHANGE heb ik een probleem want de voorwaarde is dat ik enkel de data van de exchange met de hoogste ID krijg, waar BORROWED != NULL. Als ik die voorwaarden in een WHERE clause plaats is heel het concept om zeep want die LEFT staat er nu net omdat er voor dat item gewoon geen waarde in exchange zit... en dan returned de query helemaal niets omdat die WHERE clause niet kan checken (want geen overeenkomstige data in tabel EXCHANGE)...

Ik hoop dat de lange uitleg een beetje duidelijk is? Hoe kan ik de query herschrijven zodat hij toch doet wat ik wil?

[NMe]

Verwijderd schreef op zondag 23 juli 2006 @ 14:53:
Ik gebruikte eerder een oracle database maar aangezien ik nu MySQL gebruik, moet ik de queries omzetten. In Oracle kan je bijvoorbeeld meerdere tabellen in de FROM zetten en deze dan joinen via de WHERE clause. In MySQL doe je dit echter via INNER JOIN ... ON.

MySQL kent net zo goed impliciete joins hoor.

Overigens kennen we hier ook code-tags; zie dit topic: Hoe post je code? / Hoe gebruik je de code tag?

Zie voorbeeld hieronder (Oracle query):

SQL:

SELECT OWNER.PURCHASEDATE AS PURCHASEDATE, 
U.FIRSTNAME AS B_FIRSTNAME, 
U.LASTNAME AS B_LASTNAME,
EXCHANGE.HANDINDATE AS HANDINDATE,
EXCHANGE.BORROWED AS BORROWED
FROM WIKILIB_OWNER OWNER,
--vanaf hier
(SELECT * 
FROM WIKILIB_EXCHANGE EX1, WIKILIB_OWNER O1
WHERE EX1.ID =
(SELECT MAX(EX2.ID)
FROM WIKILIB_EXCHANGE EX2, WIKILIB_OWNER O
WHERE EX2.ITEMCOPY = O.ID AND O.ID = O1.ID AND EX2.BORROWED IS NOT NULL)
) EXCHANGE,
--tot hier
WIKILIB_USER U 
WHERE EXCHANGE.ITEMCOPY(+) = OWNER.ID 
AND EXCHANGE.BORROWER = U.USERNAME(+) 
AND OWNER.ID = ?

DIt is de MySQL query:

SQL:

SELECT OWNER.PURCHASEDATE AS PURCHASEDATE, 
U.FIRSTNAME AS B_FIRSTNAME, 
U.LASTNAME AS B_LASTNAME, 
EXCHANGE.HANDINDATE AS HANDINDATE, 
EXCHANGE.BORROWED AS BORROWED 
FROM WIKILIB_OWNER OWNER 
LEFT JOIN
--vanaf hier
(SELECT * 
FROM WIKILIB_EXCHANGE EX1, WIKILIB_OWNER O1 
WHERE EX1.ID = 
(SELECT MAX(EX2.ID) 
FROM WIKILIB_EXCHANGE EX2, WIKILIB_OWNER O 
WHERE EX2.ITEMCOPY = O.ID AND O.ID = O1.ID AND EX2.BORROWED IS NOT NULL)
) EXCHANGE
--tot hier
ON EXCHANGE.ITEMCOPY = OWNER.ID
LEFT JOIN WIKILIB_USER U ON EXCHANGE.BORROWER = U.USERNAME
WHERE OWNER.ID = ?

Hoe kan ik de query herschrijven zodat hij toch doet wat ik wil?

Ik denk dat je zou moeten gaan denken aan een temporary table.

[Verwijderd]

Aha code tags, idd zeer handig

-NMe- schreef op zondag 23 juli 2006 @ 15:06:
MySQL kent net zo goed impliciete joins hoor.

Herkent MySQL dan ook het (+) symbool in een WHERE statement om een LEFT/RIGHT JOIN aan te duiden?

-NMe- schreef op zondag 23 juli 2006 @ 15:06:
Ik denk dat je zou moeten gaan denken aan een temporary table.

Hmm temporary table in MySQL. En hoe gaat dat dan in z'n werk?

[NMe]

Verwijderd schreef op zondag 23 juli 2006 @ 15:16:
Herkent MySQL dan ook het (+) symbool in een WHERE statement om een LEFT/RIGHT JOIN aan te duiden?

Volgens mij is dat Oracle-specifiek, dus ik denk het niet. Ik geef sowieso mijn joins liever expliciet aan, dat vind ik duidelijker uitzien.

Hmm temporary table in MySQL. En hoe gaat dat dan in z'n werk?

Ik heb ze zelf nooit nodig gehad, aangezien ik eerder een technisch informaticus ben dan bestuurlijk informaticus. Echter denk ik dat je er met deze pagina's wel uit moet kunnen komen:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://dev.mysql.com/doc/...orary-table-problems.html
http://www.devwebpro.com/...ry-Tables-With-MySQL.html

[_js_]

De foutmelding betekent precies wat de foutmelding zegt.

In MySQL als je een join doent op twee tabellen met beide een veld met dezelfde naam, dan geeft MySQL beide velden weer, zelfs als de join conditie op die velden ligt.

In jouw subquery krijg je dus a.id en b.id, je hernoemt dit naar exchange.id en exchange.id, dat is je duplicate column name. Zorg dat je niet * selecteert, maar alleen de velden die je nodig hebt, en je hebt dit probleem niet.

[Verwijderd]

Hangt toch ook af van de MySql versie?
"Oude versies" kennen toch geen subqueries?

[Verwijderd]

Subquery's zijn niet het probleem hoor. Het probleem is het feit dat ik wil joinen op een esultset en niet op een volledige tabel...

In oracle is dat totaal geen probleem. Maar Oracle Db's kosten geld he, en MySql niet, dus had ik graag een werkende MySql oplossing gehad ;-)

[Verwijderd]

PostgreSQL en Firebird zijn ook gratis, en kennen een stuk minder beperkingen dan MySQL...

[GX]

Ik vraag me af waarom je niet gewoon joined op de tabel uit je from; en dan in de where de resultset beperkt.

[ACM]

Verwijderd schreef op zaterdag 12 augustus 2006 @ 19:17:
Subquery's zijn niet het probleem hoor. Het probleem is het feit dat ik wil joinen op een esultset en niet op een volledige tabel...

Een query levert per definitie een tabel op... Dus ik denk dat je de foutmelding wat beter moet zien te interpreteren. Als je niet had mogen joinen tegen "een resultset" (een generated table is misschien een betere term) zou dat er als foutmelding staan.

In oracle is dat totaal geen probleem. Maar Oracle Db's kosten geld he, en MySql niet, dus had ik graag een werkende MySql oplossing gehad ;-)

Volgens mij had er hier in dit topic al iemand uitgelegd hoe je het op zou moeten kunnen lossen... en sowieso wat de bron van het probleem is. Wat me verbaast is dat je in staat bent een tweevoudig geneste query te schrijven, maar blijkbaar niet om de fout met de informatie van _js_ op te lossen

Maar ter illustratie, wat je basically doet met je subselect is een table genereren, bedenk je of zoiets toegestaan zou zijn:

SQL:

CREATE TABLE test
(
  id integer,
  id integer
);

Zonee.. waarom denk je dat het in je table generating subselect wel zou moeten mogen?

[Confusion]

Overigens moet je in MySQL zo'n subquery in een join verplicht een naam geven, dus

SQL:

SELECT aColumn, someColumnFromAnotherTable.someColumn FROM aTable JOIN (SELECT someColumn FROM anotherTable) as someColumnFromAnotherTable ON (aTable.anID = anotherTable.someID)

edit:
Nou, zoals ACM hieronder zegt: met een expliciete AS is het duidelijker ... of ik moet gewoon beter opletten

[ Voor 33% gewijzigd door Confusion op 13-08-2006 21:09 ]

[ACM]

Confusion schreef op zondag 13 augustus 2006 @ 16:36:
Overigens moet je in MySQL zo'n subquery in een join verplicht een naam geven, dus

SQL:

SELECT aColumn, someColumnFromAnotherTable.someColumn FROM aTable JOIN (SELECT someColumn FROM anotherTable) as someColumnFromAnotherTable ON (aTable.anID = anotherTable.someID)

Dat deed ie ook allemaal goed. Hoewel het met die expliciete 'AS' natuurlijk nog net iets duidelijker zou zijn.