[MySQL] Gruwelijke query die niet lijkt te doen wat 'ie moet

[Sp3ci3s8472]

Hallo medetweakers,

Ik ben bezig met een online game waarbij we als onderliggend systeem het d12 system gebruiken waar DnD ook op draait.

Op dit moment loop ik tegen een probleem aan met een query die ik heb.:

SQL:

SELECT s.*, c.skillid AS s_skillid, r.*
FROM game_skills s
LEFT JOIN game_l_classskills c USING (skillid)
LEFT JOIN game_l_raceskills r USING (skillid)
WHERE c.classid = $classid
OR r.raceid = $raceid

Ik heb deze tabellen:

game_skills(skillid, skillname, skillinfo)
game_l_raceskills(raceid,skillid)
game_l_classskills(classid,skillid)

classid en raceid kunnen dus een getalletje zijn, die het classid en het raceid aangeven.

In de _l_ tabellen (linktabellen dus koppeltabellen) staat aangegeven als een bepaalde skill bij een bepaald race of class hoort. Nu wil ik dus een overzicht ophalen waarin alle skills staan, dus gewoon een kopie van game_skills, en als een bepaalde skill bij een bepaalde race hoort, moet deze 'geflagged worden' door de tabel game_l_raceskills of game_l_classskills er aan vast te joinen.

Dit lukt echter niet, ik krijg met de verschillende queries die ik heb weten te fabriceren OF een empty resultset terug, OF maar de helft van de bestaande skills...

[r0bert]

In ieder geval een idee

SQL:

ON s.killid = c.skillid

 ==

USING (skillid)

[Sp3ci3s8472]

Dat weet ik (Nouja bijna dan vanochtend had ik zonder haakjes en toen mocht het niet...)

[GlowMouse]

Sp3ci3s8472 schreef op donderdag 07 augustus 2008 @ 17:31:
Dit lukt echter niet, ik krijg met de verschillende queries die ik heb weten te fabriceren OF een empty resultset terug, OF maar de helft van de bestaande skills...

Ik zie maar één query en ik zie niet wat daar fout mee gaat. Heb je wat testgegevens, welke output je verwacht, en welke output je krijgt?

[_js_]

Je WHERE voorwaarden doe je op de linktabellen. Mocht er geen link zijn voor een bepaalde skill dan is de waarde van die link NULL. In combinatie met die WHERE gaat dat dus niet goed werken.

Ik denk dat als je je voorwaarden in de join zet je dan wel het resultaat krijgt wat je zoekt.

Voorbeeld:

SQL:

SELECT ...
FROM game_skills s
LEFT JOIN class c ON s.skillid = c.skillid AND c.classid = $classid
LEFT JOIN race r ON s.skillid = r.skillid AND r.raceid = $raceid

[Sp3ci3s8472]

game_skills:

skillid	skillname	skilldescription
1	spot	Necessary to spot
2	listen	Necessary to hear .
3	hide	Enables you to hide (sneak)
4	move silently	Move without making sound.

game_l_classskills:

skillid	classid
2	2
3	5

game_l_raceskills:

skillid	raceid	skillmodifier
3	4	1
5	5	1

Met de volgende query:

SQL:

SELECT s.*, c.skillid AS s_skillid, r.*
FROM game_skills s
LEFT JOIN game_l_classskills c USING (skillid)
LEFT JOIN game_l_raceskills r USING (skillid)
WHERE c.classid = 2
OR r.raceid = 4

In dit voorbeeld is mijn class een roque (heeft id 2) waardoor ik in het voorbeeld de skill listen krijg.
ik ben van het race halfling( heeft raceid 4) , waardoor ik 1 extra punt extra in hide krijgt.

Ik wil dan deze tabel terugkrijgen:

skillid	skillname	skilldescription	skillmodifier	classkill
1	spot	Necessary to spot	0	0
2	listen	Necessary to hear .	0	1
3	hide	Enables you to hide (sneak)	1	0
4	move silently	Move without making sound.	0	0

[GlowMouse]

Dat gaat met die WHERE dus al nooit lukken, want dan krijg je alleen de rijen terug waarvoor geldt c.classid = 2 OR r.raceid = 4. Wat _js_ aandraagt is dan al een stuk beter. In je applicatie kun je die 0/1 er wel bijplakken, en anders moet je met IF(raceid>0,1,0) aan de slag.

[Sp3ci3s8472]

Hoppa inmiddels zijn we een heel stuk verder hiero, we gaan nu ff kijken naar de IF want we willen eigenlijk gewoon mooi 0 of 1 terug Thx allemaal !

[Borizz]

_js_ schreef op donderdag 07 augustus 2008 @ 18:08:
Je WHERE voorwaarden doe je op de linktabellen. Mocht er geen link zijn voor een bepaalde skill dan is de waarde van die link NULL. In combinatie met die WHERE gaat dat dus niet goed werken.

Ik denk dat als je je voorwaarden in de join zet je dan wel het resultaat krijgt wat je zoekt.

Voorbeeld:

SQL:

SELECT ...
FROM game_skills s
LEFT JOIN class c ON s.skillid = c.skillid AND c.classid = $classid
LEFT JOIN race r ON s.skillid = r.skillid AND r.raceid = $raceid

Ik denk dat je eens goed naar de JOIN syntax van Mysql moet kijken want wat jij daar doet kan echt niet in MySql en ook niet in normaal SQL.

[Sp3ci3s8472]

Het werkt anders wel perfect, dus dat het in MySQL kan is inmiddels bewezen

[Borizz]

Sp3ci3s8472 schreef op donderdag 07 augustus 2008 @ 18:36:
Het werkt anders wel perfect, dus dat het in MySQL kan is inmiddels bewezen

Ik zie het inderdaad, ik was te snel met reageren. De handleiding zegt echter wel het volgende:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

M.a.w. je zou het eigenlijk enkel moeten gebruiken voor het koppelen van de tabellen en niet om resultaten te filteren.

[zeroxcool]

Sp3ci3s8472 schreef op donderdag 07 augustus 2008 @ 18:36:
Het werkt anders wel perfect, dus dat het in MySQL kan is inmiddels bewezen

Wat Borizz bedoelt is dat je join condities, en where condities mixt in een join conditie. Het kan misschien wel werken in MySQL, maar het hoort niet. Maak er dus dit van:

SQL:

SELECT ...
FROM game_skills s
LEFT JOIN class c ON s.skillid = c.skillid
LEFT JOIN race r ON s.skillid = r.skillid
WHERE c.classid = $classid AND r.raceid = $raceid

Zorg er natuurlijk voor dat $classid en $raceid ge-escape'd worden e.d.

[edit]spuit 11

[Sp3ci3s8472]

Nee, dat is wat ik eerst had, hier krijg ik niet alle overige resultaten ook mee terug.

[zeroxcool]

Sp3ci3s8472 schreef op donderdag 07 augustus 2008 @ 19:12:
Nee, dat is wat ik eerst had, hier krijg ik niet alle overige resultaten ook mee terug.

Maakt de AND niet het verschil dan?

[GlowMouse]

Borizz schreef op donderdag 07 augustus 2008 @ 18:52:
M.a.w. je zou het eigenlijk enkel moeten gebruiken voor het koppelen van de tabellen en niet om resultaten te filteren.

Nee, wat er staat is precies wat TS wil. Hij wil alle rijen uit game_skills, dus dan moet je niet met een WHERE gaan filteren.

[Spiral]

zeroxcool schreef op donderdag 07 augustus 2008 @ 19:22:
[...]

Maakt de AND niet het verschil dan?

Kijk hier wordt het op een simpele manier uitgelegd http://www.sqlteam.com/ar...iteria-in-the-join-clause

[_js_]

zeroxcool schreef op donderdag 07 augustus 2008 @ 18:52:
[...]

Wat Borizz bedoelt is dat je join condities, en where condities mixt in een join conditie. Het kan misschien wel werken in MySQL, maar het hoort niet. Maak er dus dit van:
...
Zorg er natuurlijk voor dat $classid en $raceid ge-escape'd worden e.d.

[edit]spuit 11

Zoals eerder gezegd is het gewoon valide SQL, en werkt in alle gangbare databases, alleen in MS Access moet je extra haakjes gebruiken wanneer je meerdere join voorwaarden hebt.

Een extra voorwaarde in een join zetten is eigenlijk een afkorting voor joinen op een select met daarin de voorwaarde in de where.

Dus

SQL:

SELECT *
FROM TabelA a
LEFT JOIN TabelB b ON a.a = b.a AND b.x = 1

is een kortere manier om het volgende te schrijven:

SQL:

SELECT *
FROM Tabel a
LEFT JOIN (
  SELECT * FROM TabelB WHERE x = 1
) AS b ON a.a = b.a

Zo is het misschien ook duidelijker wat topicstarter wil.