Joining Two Rows (single table) to Single Row, Multiple columns

Question

Is there any way to JOIN two rows from a single table to a single row, based on two columns.

I built a scrappy little data base for my business, and I finally ran into a situation where I thought I had built in the necessary extensibility. Unfortunately, I found out pretty fast that I can't use the JOIN as I was before now that I need to join info from two columns to a single row (previously, I was only joining on one column).

Here is what I have:

| Name |    ID   |    ID_2 |   
|----- |---------| --------|   
| Jake |    a    |    b    |   
| Jane |    b    |    c    |  
| John |    c    |    NULL |



ID   | Info
-----------
a    |  1
b    |  3
c    |  5

And here is what I'm looking for:

Name | Info_1  |    Info_2
--------------------------
Jake |    1    |    3    
Jane |    3    |    5
John |    5    |    NULL

Or, better yet, this:

Name | Info_Sum
---------------
Jake |    4    
Jane |    8    
John |    5    

Answer 1

You Simply have to do a Two JOINs on the same Table

SELECT n.name, i1.info AS info_1, i2.info AS info_2
FROM names n
LEFT JOIN info i1 ON i1.ID = n.ID
LEFT JOIN info i2 ON i2.ID = n.ID_2

Just like here http://sqlfiddle.com/#!9/9f703e/1/0

---

For the Summed one, You have to use COLEACES to calculate the NULL values like the following

SELECT n.name, (COALESCE(i1.info, 0) + COALESCE(i2.info, 0)) AS Info_Sum
FROM names n
LEFT JOIN info i1 ON i1.ID = n.ID
LEFT JOIN info i2 ON i2.ID = n.ID_2

Just like here http://sqlfiddle.com/#!9/9f703e/3/0

---

In your next question I hope if you create the Table in SQLFiddle or dbfiddle and share it Or put the CREATE code so we can built the Table quickly to provide faster answers.

Answer 2

Join twice on table2 (or whatever you call it) and use COALESCE to handle NULLs as zeroes.

SELECT t1.Name, (
      COALESCE(t2a.Info, 0) + 
      COALESCE(t2b.Info, 0)
     ) AS Info_Sum
  FROM table1 AS t1
  JOIN table2 AS t2a ON t1.ID = t2a.ID
  JOIN table2 AS t2b ON t1.ID_2 = t2a.ID;