0

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    
3

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.

|improve this answer|||||
  • Thanks for taking the time and (presumably) noticing that I'm not a regular. Point taken about CREATE code. – Kale Apr 20 '18 at 15:06
0

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;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.