[Mysql] Probleem met opmaak SELECT query

[japaveh]

Ik probeer een MySQL query op te zetten waarbij meerdere tabellen gekoppeld worden middels een WHERE voorwaarde.

Het is voor een competitiesysteem, waarbij het probleem onstaat tussen de koppeling van 2 tabellen. Deze tabellen zijn hier nu vereenvoudigd

tabel 1 (teams) heeft als structuur:
team_id, team_naam

en tabel 2 (wedstrijden) heeft als structuur:
wedstrijd_id ... ... wedstrijd_thuisploeg_id, wedstrijd_uitploeg_id ... ...

Nu is het de bedoeling dat er 1 query gedaan wordt waarbij deze 2 tabellen gekoppeld wordt zodat ik als output een array krijg met direct de naam van de thuisploeg en uitploeg. Hier ontstaat echter het probleem. Het kan natuurllijk zijn dat dit niet kan en dat ik na de eerste query gewoon 2 extra querys moet doen, maar ik heb dit liever in 1 keer.

In dit soort gevallen gebruik ik altijd:

code:

SELECT
       Wedstrijd_id, 
       team_naam, ... 
FROM 
       teams, 
       wedstrijden 
WHERE 
      wedstrijd.wedstrijd_thuisploeg = teams.team_id 
AND 
      wedstrijd.wedstrijd_uitploeg = teams.team_id

Maar omdat mysql nu geen onderscheid meer kan maken tussen een uitploeg en een thuisploeg geeft deze query geen resultaat. Hoe maak ik mysql duidelijk dat er verschil is tussen een uit en een thuisploeg, maw wat zet ik achter de SELECT. Kan dit uberhaupt met een SELECT * FROM bla WHERE query, of heb ik andere middelen nodig

[ Voor 3% gewijzigd door japaveh op 20-02-2005 23:19 ]

[Gert]

Je moet dan de team tabel 2x joinen waarbij je voor elk team een aparte alias maakt van de tabel. MySql ziet dan het verschil tussen de twee teams.

[crisp]

idd, 2x een left join:

code:

SELECT
    wedstrijd_id,
    thuis.team_naam AS thuis_team_naam,
    uit.team_naam AS uit_team_naam,
    ...
FROM
    wedstrijden
LEFT JOIN
    teams thuis ON wedstrijd_thuisploeg = thuis.team_id
LEFT JOIN
    teams uit ON wedstrijd_uitploeg = uit.team_id

hoewel ditzelfde principe ook wel met een fullEQUI join kan:

code:

SELECT
    wedstrijd_id,
    thuis.team_naam AS thuis_team_naam,
    uit.team_naam AS uit_team_naam,
    ...
FROM
    wedstrijden,
    teams thuis,
    teams uit
WHERE
    wedstrijd_thuisploeg = thuis.team_id
AND
    wedstrijd_uitploeg = uit.team_id

[ Voor 41% gewijzigd door crisp op 20-02-2005 00:34 ]

[japaveh]

Hmm, met zoiets was ik al bezig geweest maar ik wist dus niet of ik al op de goede weg was . Ik zal me eens verdiepen in de goede syntax van zo'n query dan.

update: Crisp

Crisp: Bij de eerste query, met de dubbele LEFT join, krijg ik zoveel resultaten als er teams in m'n teams tabel staan. Ik kan me niet voorstellen dat dat met een distinct opgelost moet worden . Je tweede voorstel voldoet wel prima. Als ik het goed begrijp werk je dan met een soort alias voor je tabel teams achter je FROM veld. Ik vind het dan wel raar dat je dat zonder AS doet...dat zou me logischer geleken hebben, maar dat kan ook weer aan mij liggen

Nog eventjes terugkomen. Je mag dus achter je FROM in de query ook gerust:

code:

SELECT
    wedstrijd_id,
    thuis.team_naam AS thuis_team_naam,
    uit.team_naam AS uit_team_naam,
    ...
FROM
    wedstrijden,
    teams AS thuis,
    teams AS uit
WHERE
    wedstrijd_thuisploeg = thuis.team_id
AND
    wedstrijd_uitploeg = uit.team_id

schrijven. Dit is dan weer consistent met de AS achter de SELECT

[ Voor 92% gewijzigd door japaveh op 20-02-2005 00:45 ]

[Gert]

AS is niet verplicht, dat wisselt per rdbms.

[Soultaker]

Volgens mij is in de standaard vastgelegd dat dat niet verplicht is. Het wisselt dus niet per RDBMS en als japaveh het overzichtelijker vindt om het wel neer te zetten, dan is daar weinig op tegen. (Ik doe het zelf ook altijd.)

Voor de TS: het is misschien wel interessant/nuttig om uit te zoeken waarom crisp's eerste oplossing niet werkte. (Dat geeft wel een nuttig inzicht in hoe die joins werken.) Ik raad zelf trouwens zelden aan de join-syntax te gebruiken. Intern wordt het toch allemaal naar hetzelfde geoptimaliseerd en bij de join-syntax is naar mijn ervaring vaak veel onduidelijker wat er nu precies gebeurt (wat dat betreft is het geen verrassing dat juist die query niet klopt).

[ Voor 51% gewijzigd door Soultaker op 20-02-2005 17:06 ]

[Gert]

Ik vind juist het tegen over gestelde. Zeker als je complexe joins met joins op joins op joins krijgt, dan is de structuur duidelijker.
Maarja, dat is erg persoonlijk natuurlijk. Daarnaast heb ik gemerkt dat de profiler deze beter slikte maar dat kan natuurlijk ook aan het datamodel en de profiler zelf liggen.

code:

SELECT * FROM
table
INNER JOIN (a 
  INNER JOIN (b
    INNER JOIN c USING cid
  ) USING bid
) USING aid

[japaveh]

Nog eventjes terugkomende op de AS na de FROM in de query. In de Mysql handleiding staat het volgende erover

quote: http://dev.mysql.com/doc/mysql/en/join.html

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

code:

mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
    ->        WHERE t1.name = t2.name;
mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
    ->        WHERE t1.name = t2.name;

Ik heb nog eventjes nagedacht over waarom ik voor de eerst oplossing evenveel waardes krijg als er rijen in de teams tabel staan. Ik vermoed dat dit te maken heeft met het feit dat er geen restricties opgelegd zijn aan de SELECT team_naam.

Ik ben op zeker nog beginnend in het creeren van query's en het zoeken van de juiste vorm ervan. Ik begrijp nu wel hoe een JOIN werkt. Als beginner is het moeilijk om het juiste commando te vinden in je query, zeker omdat je nog lang niet alles kent. Ik vermoed dat er in mijn site nu veel query's zijn die een stukje korter kunnen

Hetzelfde probleem onstaat natuurlijk bij PHP commando's maar goed, door het gewoon te gebruiken leer je steeds meer bij

[Soultaker]

japaveh schreef op zondag 20 februari 2005 @ 23:17:
Ik heb nog eventjes nagedacht over waarom ik voor de eerst oplossing evenveel waardes krijg als er rijen in de teams tabel staan. Ik vermoed dat dit te maken heeft met het feit dat er geen restricties opgelegd zijn aan de SELECT team_naam.

Hmz, nu ik er nog eens naar kijk, zie ik trouwens niet in wat er verkeerd aan is. Ik dacht dat de 'ON ...' condities niet klopten, maar volgens mij doen ze dat wel. Ik heb het lokaal even getest en daarbij lijken ze beiden goed te werken; weet je zeker dat je goed getest hebt en niet ergens een verkeerde conditie hebt overgenomen?

Trouwens, ik geloof nog niet dat het fundamentele verschil tussen een query met of zonder JOIN genoemd is: als je "A LEFT JOIN B ON C" doet en voor een zekere rij in A is er geen rij in B die aan conditie C voldoet, dan krijg je een null-row (een rij met alleen maar NULL values) terug. Bij de equivalente query met alleen een WHERE-clause zouden zulke rijen in het geheel weggelaten worden.

Concreet betekent dat dat als er een wedstrijd is waarbij een team-id gegeven wordt die niet in je teams tabel voorkomt, de query met JOIN's voor elke wedstrijd de teamnamen geeft of NULL wanneer een teamnaam niet beschikbaar is, terwijl de query met alleen een WHERE-clause wedstrijden waarbij de teamnaam van één van de twee teams niet beschikbaar is helemaal uit het resultaat weglaat.

Wat wenselijk is is van de situatie afhankelijk en voor veel toepassingen kun je wel garanderen dat als in de ene tabel aan een id gerefereerd wordt die zeker in een andere tabel aanwezig is (omdat je bijvoorbeeld een foreign key constraint hebt gedefinieerd, wat in elke fatsoenlijke RDBMS kan maar niet in MySQL) en in zo'n geval zijn de twee vormen effectief hetzelfde en is er alleen een verschil in syntax.

[japaveh]

Ik heb gevonden waarom de eerste query van Crisp in mijn geval niet werkt.

Ik had de tabel teams nog achter de FROM staan, deze heb ik weggehaald en nu geeft hij net zoveel results als er rijen in de wedstrijdentabel staan

Ik heb ook nog eventjes gekeken daar die verschillen tussen van een query met en zonder join, en inderdaad returned een query met JOIN keurig een NULL wanneer een rij niet voorkomt in m'n teamstabel. Bedankt voor de info