MySQL JOIN to replace identifiers with a value from another table

Question

Let's say I have a coding table containing various types, for example:

ID EncodingName ------------------ 1 UTF-8 2 ISO-8859-1 

And another EncodingMapping that uses these identifiers to keep track of which encodings to convert From and To:

  ID ItemId_FK EncodingFromId_FK EncodingToId_FK ------------------------------------------------- 1 45 2 1 2 78 1 2 

I want to create an SQL statement that produces the following result when ItemId_FK = 45 (for example):

  FromEncoding ToEncoding ------------------------- ISO-8859-1 UTF-8 

It seems like this would be simple enough, but I can't get the JOIN to work by returning a single line in this format.

What I still have (THIS IS WRONG):

  SELECT EncodingName As FromEncoding, EncodingName As ToEncoding FROM Encoding LEFT JOIN EncodingMapping As em ON Encoding.ID = em.EncodingFromId_FK OR Encoding.ID = em.EncodingToId_FK WHERE ItemId_FK = 45 

Answer 1

Close but not cigar:

 SELECT FromEnc.EncodingName AS FromEncoding, ToEnc.EncodingName AS ToEncoding FROM EncodingMapping INNER JOIN Encoding AS FromEnc ON FromEnc.ID=EncodingMapping.EncodingFromId_FK INNER JOIN Encoding AS ToEnc ON ToEnc.ID=EncodingMapping.EncodingToId_FK WHERE EncodingMapping.ItemId_FK = 45 

Answer 2

You just need to join the same table twice:

 SELECT from.EncodingName AS FromEncoding, to.EncodingName AS ToEncoding FROM EncodingMapping AS m LEFT JOIN Encoding AS from ON m.EncodingFromId_FK=from.ID LEFT JOIN Encoding AS to ON m.EncodingToId_FK=to.ID WHERE ItemId_FK=45 

As a side note, including the _FK extension in simple identifier columns seems a bit redundant.

Answer 3

You should have two connections, as you have in mind. One for "FromEncoding" and the other for "ToEncoding".

Like this:

 Select FromEncoding.Title , ToEncoding.Title From EncodingOrder Inner Join Encoding As FromEncoding On FromEncoding.Id = EncodingOrder.From_Encoding_Id Inner Join Encoding As ToEncoding On ToEncoding.Id = EncodingOrder.To_Encoding_Id 

Greetings