我对SQL不太感兴趣,在处理一个执行JOIN的查询时遇到以下问题,该查询必须联接两次相同的Languages表。
我正在使用MySql。我会尽力解释我的问题。
所以我有这个问题:
SELECT
LSSN.livestock_species_name AS species_name,
LSM.content AS message_content,
LSM.audio_link AS message_audio_link
FROM LivestockDetails AS LSD
INNER JOIN LsMessage AS LSM
ON LSD.ls_message_id = LSM.id
INNER JOIN Languages AS LNG
ON LSM.language_id = LNG.id
INNER JOIN LivestockSpecies AS LSS
ON LSD.live_stock_species_id = LSS.id
INNER JOIN LivestockSpeciesName AS LSSN
ON LSS.id = LSSN.livestock_species_id
INNER JOIN LNG
ON LSSN.language_id = LNG.id
WHERE LSD.live_stock_species_id = 3
AND LSD.ls_area_id = 2
AND LSD.ls_action_type_id = 1
AND LSD.ls_message_id is not null
AND LNG.id = 1
正如你所看到的,我正试图加入两次语言(别名为液化天然气)。
在这里一次(因为我有特定语言的本地化消息):
INNER JOIN LsMessage AS LSM
ON LSD.ls_message_id = LSM.id
这里还有一个(因为LSSN表包含特定语言的动物名称):
INNER JOIN LNG
ON LSSN.language_id = LNG.id
这样做是行不通的,我得到了这个错误消息:
#42000Not unique table/alias: 'LNG'
我想是因为我参加了两次同一张桌子。
我可以用这种方式做,而且效果很好:
SELECT
LSSN.livestock_species_name AS species_name,
LSM.content AS message_content,
LSM.audio_link AS message_audio_link
FROM LivestockDetails AS LSD
INNER JOIN LsMessage AS LSM
ON LSD.ls_message_id = LSM.id
INNER JOIN Languages AS LNG
ON LSM.language_id = LNG.id
INNER JOIN LivestockSpecies AS LSS
ON LSD.live_stock_species_id = LSS.id
INNER JOIN LivestockSpeciesName AS LSSN
ON LSS.id = LSSN.livestock_species_id
INNER JOIN Languages AS LNG2
ON LSSN.language_id = LNG2.id
WHERE LSD.live_stock_species_id = 3
AND LSD.ls_area_id = 2
AND LSD.ls_action_type_id = 1
AND LSD.ls_message_id is not null
AND LNG.id = LNG2.id = 1
我加入了一个"全新"的INNER JOIN Languages AS LNG2表,但随后我必须以这种方式更改WHERE子句:
AND LNG.id = LNG2.id = 1
我在问这是否是一个好的解决方案,或者是否存在使用单个表代替JOIN操作中的LNG和LNG2表的更智能的方法。
这是实现您想要的目标的另一种方法:
INNER JOIN Languages AS LNG
ON LSM.language_id = LNG.id
INNER JOIN Languages AS LNG2
ON LSM.language_id = LNG2.id AND LSSN.language_id = LNG2.id
我认为你不能将这2个联接合并为1。
完整查询:
SELECT
LSSN.livestock_species_name AS species_name,
LSM.content AS message_content,
LSM.audio_link AS message_audio_link
FROM LivestockDetails AS LSD
INNER JOIN LsMessage AS LSM
ON LSD.ls_message_id = LSM.id
INNER JOIN Languages AS LNG
ON LSM.language_id = LNG.id
INNER JOIN LivestockSpecies AS LSS
ON LSD.live_stock_species_id = LSS.id
INNER JOIN LivestockSpeciesName AS LSSN
ON LSS.id = LSSN.livestock_species_id
INNER JOIN Languages AS LNG2
ON LSM.language_id = LNG2.id AND LSSN.language_id = LNG2.id
WHERE LSD.live_stock_species_id = 3
AND LSD.ls_area_id = 2
AND LSD.ls_action_type_id = 1
AND LSD.ls_message_id is not null
AND LNG.id = 1