MySQL JOIN to replace IDs with value from another table

Question

Say I have an Encoding table that containing various types, for ex:

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

And another EncodingMapping that uses these IDs 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 creates the following result when ItemId_FK = 45 (for example):

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

Seems like it would be simple enough, but I cannot a get a JOIN to work by returning a single row in this format.

What I have so far (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

score 8 · Accepted Answer · answered Sep 14 '12 at 20:41

8

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

answered Sep 14 '12 at 20:41

Eugen Rieck

64,175
10
70
92

Thank you. Some other answers have used a LEFT JOIN instead of an INNER JOIN. Can you explain when to use which in this context? – http203 Sep 14 '12 at 20:55
2

@http203: Jeff explains it well in his blog article, [A Visual Explanation of SQL joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html). – eggyal Sep 14 '12 at 21:00

score 1 · Answer 2 · answered Sep 14 '12 at 20:42

You just need to double-join the same table:

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 note, including the _FK extension on simple ID columns seems a bit redundant.

score 0 · Answer 3 · answered Sep 14 '12 at 20:42

You should have two joins as you mean. 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

Cheers

MySQL JOIN to replace IDs with value from another table

3 Answers3

Linked