What is a smarter way to use a single table instance in a query that JOIN 2 times the same table?

Question

I am not so into SQL and I have the following problem working on a query that do some JOIN and that have to join 2 times the same Languages table.

I am using MySql. I will try to explain my problem.

So I have this query:

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

As you can see I am trying to join 2 times the Langunages (aliasted by LNG).

Once here (because I have localized messages in a specific langunage):

INNER JOIN LsMessage AS LSM
      ON LSD.ls_message_id = LSM.id 

and another one here (because the LSSN table contains the name of an animal in a specific language):

INNER JOIN LNG
      ON LSSN.language_id = LNG.id

Doing in this way it doesn't work and I am obtain this error message:

#42000Not unique table/alias: 'LNG'

I think because I am joining 2 times the same table.

I can do it in this way and it works fine:

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

I joined with a "brand new" INNER JOIN Languages AS LNG2 table but then I have to change the WHERE clause in this way:

AND LNG.id = LNG2.id = 1

I am asking if it is a good solution or if exist some smarter way to use a single table instead the LNG and LNG2 tables in the JOIN operation.

Answer 1

This is one more way you can achieve what you want:

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

I think you can't merge those 2 joins into 1.

Full query:

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