[SQL] Probleem met joins

[Matthis]

ik zit met een klein probleem bij het ontwikkelen van m'n app. Vermoedelijk is de oplossing zeer eenvoudig, maar ik kom er momenteel niet uit.

Ik heb volgende tabellen:

tabel 1: objects:
velden: object_id, object_...

tabel 2: descriptions:
velden: object_id, language, description_....


ik wil alle objecten selecteren en hieraan beschrijvingen koppelen. Eenvoudig te doen met een join. Nu wil ik in de eerste plaats alle beschrijvingen in een bepaalde taal. Simpel: WHERE language = 'nederlands'. Maar ik wil dat alle objecten weergegeven worden, ook al is er geen beschrijving in die taal.

Bijvoorbeeld: Object 2 heeft geen nederlandstalige beschrijving, maar wel een franstalige. Kan er me iemand op weg zetten dit in een query te gieten?

Via group by kan je van alle objecten 1 beschrijving krijgen, maar daarmee is m'n taalvoorkeur niet opgelost. Alfabetisch sorteren biedt ook geen uitkomst, een 'custom sort' zou dit wel bieden...

[FlorisB]

je hebt geen primary key bij descriptions, tenzij dit object_id is, maar dat kan niet omdat je dan maar 1 description kan toevoegen.

Voeg iets als description_id toe.


Oh btw, als je alle objecten wilt hebben ook als er geen beschrijving is gebruik je LEFT JOIN zo even uit mijn hoofd.

[ Voor 39% gewijzigd door FlorisB op 10-01-2008 22:57 ]

[Matthis]

1/ ik heb niet al m'n velden opgesomd. tuurlijk is er een description_id en nee, 'nederlands' staat niet hardcoded in de db, ...

2/ left join: ok, maar hoe specifieer je dan je voorkeurstaal? WHERE language = 'ndl', maar daarmee doe je je left join teniet, toch?

[FlorisB]

Ja hoeveel talen heb je? Sorteren op NLD gaat moeilijk worden, je zou wel een taal ID kunnen maken, en dit in een aparte tabel zetten. (met prioriteit, dus taal_id, taal_naam, taal_prio)

Je kunt dan dmv een LEFT en INNER join gaan sorteren op je taal_prio


edit:
Je zou dan het volgende krijgen:

tabel 1: objects:
velden: object_id, object_...

tabel 2: descriptions:
velden: object_id, language_id, description_....

tabel 3: languages:
velden: language_id, language_name, language_prio

SELECT objects.*, descriptions.*, languages.*
FROM objects
LEFT JOIN descriptions ON descriptions.object_id = objects.object_id
INNER JOIN languagges ON languages.language_id = descriptions.language_id
ORDER BY language_prio ASC

Klopt niet helemaal, maar je komt er een heel eind mee.

[ Voor 53% gewijzigd door FlorisB op 10-01-2008 23:21 ]

[RobIII]

Matthis schreef op donderdag 10 januari 2008 @ 23:00:
2/ left join: ok, maar hoe specifieer je dan je voorkeurstaal? WHERE language = 'ndl', maar daarmee doe je je left join teniet, toch?

code:

left outer join descriptions on descriptions.object_id = objects.object_id and language = 'nl'

[ Voor 15% gewijzigd door RobIII op 11-01-2008 00:17 ]

[eamelink]

* Een left join op alle talen
* Een case/if/whatever op taal = voorkeurstaal en daarop sorteren
* Sorteren op een alternatieve taalvolgordelijst
* Groupen op object_id

Dan krijg je dus zoiets :

SQL:

SELECT object.*, 
    descriptions.*, 
    IF(descriptions.language = '$voorkeurstaal', 1,0) as pref_language 
    FROM objects 
    LEFT JOIN descriptions 
      ON objects.object_id = descriptions.object_id 
    ORDER by pref_language DESC 
    GROUP BY object.id

Als je nog een standaard taalvolgorde voor alternatieve talen wilt toevoegen moet je die ook nog even joinen op descriptions.language en daar een tweede ORDER BY voor maken

[ Voor 59% gewijzigd door eamelink op 11-01-2008 00:36 ]

[P.O. Box]

is zoiets dan niet handiger?

SQL:

select
    coalesce(taaltabel1.description, taaltabel2.description, taaltabel3.description,....) as description
from
    objects
left join
    descriptions taaltabel1
on
    objects.object_id = descriptions.object_id AND descriptions.language = '$voorkeurstaal1'
left join
    descriptions taaltabel2
on
    objects.object_id = descriptions.object_id AND descriptions.language = '$voorkeurstaal2'
left join
    descriptions taaltabel3
on
    objects.object_id = descriptions.object_id AND descriptions.language = '$voorkeurstaal3'
:
:
:

[Matthis]

eamelink schreef op vrijdag 11 januari 2008 @ 00:31:
* Een left join op alle talen
* Een case/if/whatever op taal = voorkeurstaal en daarop sorteren
* Sorteren op een alternatieve taalvolgordelijst
* Groupen op object_id

Dan krijg je dus zoiets :

SQL:

SELECT object.*, 
    descriptions.*, 
    IF(descriptions.language = '$voorkeurstaal', 1,0) as pref_language 
    FROM objects 
    LEFT JOIN descriptions 
      ON objects.object_id = descriptions.object_id 
    ORDER by pref_language DESC 
    GROUP BY object.id

Als je nog een standaard taalvolgorde voor alternatieve talen wilt toevoegen moet je die ook nog even joinen op descriptions.language en daar een tweede ORDER BY voor maken

sorry voor de enorme kick, maar het projectje was "wat" in de koelkast beland, waardoor deze query nooit geïmplementeerd raakte.

Op zich doet de query op papier wat ik wil, alleen moet de ORDER BY ná de GROUP BY komen in MySQL waardoor deze oplossing alsnog niet werkt. Zelf raak ik er niet aan uit, dus moest er iemand alsnog een oplossing weten...

[Voutloos]

Matthis schreef op dinsdag 24 maart 2009 @ 13:00:
Op zich doet de query op papier wat ik wil, alleen moet de ORDER BY ná de GROUP BY komen in MySQL waardoor deze oplossing alsnog niet werkt. Zelf raak ik er niet aan uit, dus moest er iemand alsnog een oplossing weten...

Je wilt een groupwise maximum (zoekterm!)

[Matthis]

Dit is het geworden:

SQL:

SELECT object_id, IF(t1.language = '$voorkeurstaal', 1, 0) AS pref_language
FROM objects t1
HAVING pref_language = (
SELECT MAX (IF(t2.language = '$voorkeurstaam', 1, 0) ) 
FROM objects t2
WHERE t1.object_id = t2.object_id
)

kan vermoedelijk nog opgeschoond worden, maar het werkt nu toch al ...

[Alain]

Ik zie dat het veld language nu in de tabel objects staat en dat de tabel descriptions niet meer mee doet. Lijkt me raar. Daarnaast snap ik niet echt wat je wilt selecteren als er bijvoorbeeld een engelse en franse beschrijving is, maar geen beschrijving in de voorkeurstaal.

Wat ik begrijp uit je verhaal wil je alle objecten met description selecteren met een description in de voorkeurstaal. Daar wil je alle objecten met hun description aan toevoegen die geen description hebben in de voorkeurstaal. In dit geval lijkt me een union op zijn plaats.

Ik zou het zo oplossen:

SQL:

SELECT o.object_id, description_id, language
FROM objects o
JOIN descriptions d
ON o.object_id = d.object_id
WHERE language = 'NL'
UNION
SELECT o.object_id, description_id, language
FROM objects o
JOIN descriptions d
ON o.object_id = d.object_id
WHERE o.object_id NOT IN (
  SELECT DISTINCT object_id
  FROM descriptions
  WHERE language = 'NL')
ORDER BY object_id;

De join ziet er nu nogal zinloos uit, maar ik neem aan dat je meer velden uit de tabel objects weer wilt geven.

NB, als je ook objecten wilt selecteren die geen description hebben moet je er een LEFT OUTER JOIN van maken.

Test tabellen:

code:

+-----------+
| object_id |
+-----------+
|         1 |
|         2 |
|         3 |
|         4 |
+-----------+

+----------------+-----------+----------+
| description_id | object_id | language |
+----------------+-----------+----------+
|              1 |         1 | NL       |
|              2 |         1 | EN       |
|              3 |         2 | FR       |
|              4 |         2 | EN       |
|              5 |         3 | NL       |
+----------------+-----------+----------+

Test resultaat:

code:

+-----------+----------------+----------+
| object_id | description_id | language |
+-----------+----------------+----------+
|         1 |              1 | NL       |
|         2 |              3 | FR       |
|         2 |              4 | EN       |
|         3 |              5 | NL       |
+-----------+----------------+----------+