U
Ueli Preisig
Guest
I've a problem and couldn't find a solution for it, maybe you can help:
Table 1 'rezept'
with primary_key 'id'
and fields 'flav1', 'flav2', 'flav3', ... 'flav7'
and several other fields.
Table 2 'sortiment'
with primary_key 'id'
and several other fields (sort_name_ge, sort_name_fr).
Fields 'flav1', 'flav2', ... 'flav7' are foreign keys to table 'sortiment', so I can choose up to 7 flavours ('flav') that are contained in a recipie ('rezept').
No problem to insert new records, this works perfect, but displaying the list and submitting a search doesn't work, because it seems not possible to get all the results in one single SQL-query.
Here is the SQL-Statement that get's executed:
SELECT
`rezept`.`id`,
`rezept`.`rez_name`,
`rezept`.`flav1`,
`rezept`.`flav2`,
`rezept`.`flav3`,
`rezept`.`flav4`,
`rezept`.`flav5`,
`rezept`.`flav6`,
`rezept`.`flav7`,
`rezept`.`ingredients`, `rezept`.`preparation`,
`rezept`.`category`,
`rezept`.`saison`,
`rezept`.`month`,
`rezept`.`language`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`drink_kategorie`.`drink_name_ge`,
`drink_kategorie`.`drink_name_fr`,
`saison`.`saison_name_ge`,
`saison`.`saison_name_fr`
FROM rezept
LEFT JOIN `sortiment` ON `rezept`.`flav1` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav2` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav3` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav4` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav5` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav6` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav7` = `sortiment`.`id`
LEFT JOIN `drink_kategorie` ON `rezept`.`category` = `drink_kategorie`.`id`
LEFT JOIN `saison` ON `rezept`.`saison` = `saison`.`id`
Producing the following Error:
MySQL server said: Not unique table/alias: 'sortiment'
Is there a workaround to fix this problem?
Of course, I could not use 'Select Single' to choose the flavours ('flav') and just enter the corresponding flavour id manually, but this isn't very user-friendly.
Any help would be great. I'm sure I'm not the only one with this problem.
Thx in advance!
Table 1 'rezept'
with primary_key 'id'
and fields 'flav1', 'flav2', 'flav3', ... 'flav7'
and several other fields.
Table 2 'sortiment'
with primary_key 'id'
and several other fields (sort_name_ge, sort_name_fr).
Fields 'flav1', 'flav2', ... 'flav7' are foreign keys to table 'sortiment', so I can choose up to 7 flavours ('flav') that are contained in a recipie ('rezept').
No problem to insert new records, this works perfect, but displaying the list and submitting a search doesn't work, because it seems not possible to get all the results in one single SQL-query.
Here is the SQL-Statement that get's executed:
SELECT
`rezept`.`id`,
`rezept`.`rez_name`,
`rezept`.`flav1`,
`rezept`.`flav2`,
`rezept`.`flav3`,
`rezept`.`flav4`,
`rezept`.`flav5`,
`rezept`.`flav6`,
`rezept`.`flav7`,
`rezept`.`ingredients`, `rezept`.`preparation`,
`rezept`.`category`,
`rezept`.`saison`,
`rezept`.`month`,
`rezept`.`language`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`drink_kategorie`.`drink_name_ge`,
`drink_kategorie`.`drink_name_fr`,
`saison`.`saison_name_ge`,
`saison`.`saison_name_fr`
FROM rezept
LEFT JOIN `sortiment` ON `rezept`.`flav1` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav2` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav3` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav4` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav5` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav6` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav7` = `sortiment`.`id`
LEFT JOIN `drink_kategorie` ON `rezept`.`category` = `drink_kategorie`.`id`
LEFT JOIN `saison` ON `rezept`.`saison` = `saison`.`id`
Producing the following Error:
MySQL server said: Not unique table/alias: 'sortiment'
Is there a workaround to fix this problem?
Of course, I could not use 'Select Single' to choose the flavours ('flav') and just enter the corresponding flavour id manually, but this isn't very user-friendly.
Any help would be great. I'm sure I'm not the only one with this problem.
Thx in advance!