CODEWITHSUNDEEP
mysqlsqldatabasejoinleft-join
AndreaNobili
AndreaNobili

Reputation: 42957

What is wrong in this LEFT JOIN use?

I have a litle doubt about the use of the LEFT JOIN.

I have this query:

SELECT
    LD.id                                   AS disease_id,
    LD.disease_name_en                      AS disease_name_en,
    LDN.disease_name                        AS disease_name,
    LDN.description                         AS description,
    LD.image_link                           AS image_link
FROM LsDiseases                             AS LD
LEFT JOIN LsDiseaseName                    AS LDN
      ON LDN.disease_id = LD.id

WHERE 
     LD.id = 1 
AND
     LDN.language_id = 1

returning an output like this:

disease_id           disease_name_en          disease_name             description      image_link                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------
1                    Newcastle disease        Newcastle disease        TEST             https://firebasestorage.googleapis.com/XXX

As you can see these fields:

came from the LsDiseaseName that is a table containing translation for the record into the LsDisease table.

Ok it works. I used a LEFT JOIN because my idea was: "if exist a translation into the LsDiseaseName table (for a record of LsDisease table) these field will be valorized, if the translation doesn't exist return only the information into the LsDisease table and the previous field of the LsDiseaseName will be valorized with null value.

The problem is that changint this WHERE clause:

LDN.language_id = 3

using the id of a language for which I do not have translation

I am obtaining an empty resultset. So, what is wrong in my reasoning? What am I missing?

Upvotes: 0

Views: 69

Answers (3)

Deepak Sharma
Deepak Sharma

Reputation: 4170

Actually first you are applying the join and then filtering the final result set for id = 3 and thats the reason you are getting empty result set.

you should language filtering at the time of join itself.

SELECT
    LD.id                                   AS disease_id,
    LD.disease_name_en                      AS disease_name_en,
    LDN.disease_name                        AS disease_name,
    LDN.description                         AS description,
    LD.image_link                           AS image_link
FROM LsDiseases                             AS LD
LEFT JOIN LsDiseaseName                    AS LDN
      ON LDN.disease_id = LD.id AND LDN.Language_id = 3

WHERE 
     LD.id = 1

Upvotes: 1

Stack Overflow
Stack Overflow

Reputation: 2764

Move this line into the Left join 

LEFT JOIN LsDiseaseName                    AS LDN
      ON LDN.disease_id = LD.id
      AND LDN.language_id = 3

Upvotes: 1

Andrew
Andrew

Reputation: 27294

Placing the LDN.Language_id = 3 in the WHERE clause changes the meaning of the left join to an inner join (because it does not allow for a null value)

Move that clause to the join statement

LEFT JOIN LsDiseaseName                    AS LDN
      ON LDN.disease_id = LD.id
      AND LDN.Language_id = 3

Upvotes: 1

Related Questions