mysql result in two columns from same table without duplicate rows

Question

I don't know how to ask this correctly - it was probably asked many times. I have two tables. Companies and budgets.

Companies [id,name,address]
Budgets [id,company_id,year,budget]

I want to create a select where I would get the result for each company and budgets for each year in the same row.

budgets table:

id | company_id | year | budget  
1  | 1          | 2018 | 1500   
2  | 1          | 2019 | 2500  
3  | 2          | 2018 | 700  
4  | 2          | 2019 | 6000

So I would like to get the budgets in year columns

SELECT
a.id,
a.`name`,
IF(b.season_id = 1,b.budget,'') AS "budget 2018",
IF(b.season_id = 2,b.budget,'') AS "budget 2019"

FROM companies AS a
INNER JOIN budgets AS b ON a.id = b.company_id

This of course returns double rows. :)

company name | budget 2018 | budget 2019  
company one  | 1500        | 
company one  |             | 2500  
company two  | 700         | 
company two  |             | 6000

How can I get the budgets in the same row?

company name | budget 2018 | budget 2019  
company one  | 1500        | 2500  
company two  | 700         | 6000

Or how is this procedure called/described so I can ask uncle google? :)

Edit: I got suggestions for solving this task with outer joins. Truthfully I haven't had the time to solve the problem with outer joins - not yet confident if I can trust the result. The other thing is that I find the title of the suggested solution somewhat misleading. I ended solving this problem with the use of "fake" aggregates. Because this is a task I need once a year for a custom export I won't be pursuing a "better" solution. Thank you for your input.

Does this answer your question? SQL - Select rows from two different tables — Klaas Weerstand
– Klaas Weerstand, Commented Nov 15, 2019 at 19:52
Thank you for the link. I got it working with "fake" max agregate. In my case there are no empty rows. The problem was that the rows duplicated for every instance of budget year. I think there is a working solution with outer join but I haven't had the time for learning a new trick. Anyway thank you for your help. — user805528
– user805528, Commented Nov 19, 2019 at 13:02

forpas · Accepted Answer · 2019-11-15 11:59:24Z

1

With conditional aggregation:

SELECT
  c.id,
  c.name,
  MAX(CASE WHEN b.season_id = 1 THEN b.budget END) AS `budget 2018`,
  MAX(CASE WHEN b.season_id = 2 THEN b.budget END) AS `budget 2019`
FROM companies AS c INNER JOIN budgets AS b 
ON c.id = b.company_id
GROUP BY c.id, c.name

I use MAX() because in your sample data there is only 1 row for each company/year.
If this is not the case and you want the total for each company/year then use SUM().

answered Nov 15, 2019 at 11:59

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user805528 Over a year ago

Hi I think your soltion is better than just (fake) SUM because I actually have multiple table with budgets (different budget types).

ScaisEdge · Accepted Answer · 2019-11-15 11:57:18Z

1

You could use a (fake) aggrgation function

    SELECT
    a.id,
    a.`name`,
    sum(IF(b.season_id = 1,b.budget,'')) AS `budget 2018`,
    sum(IF(b.season_id = 2,b.budget,'')) AS `budget 2019`
    FROM companies AS a
    INNER JOIN budgets AS b ON a.id = b.company_id
    GROUP BY a.id, a.name

answered Nov 15, 2019 at 11:57

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

4 Comments

slaakso Over a year ago

You could use NULL instead of '' in the IF-clause to avoid unnecessary datatype conversions from numeric to char and back to numeric.

user805528 Over a year ago

Hi I tried both and get the same correct results with both. This is used once a year for an export so optimisation is not key here. But thank you for the additional explanaton.

user805528 Over a year ago

A ran across another problem. I actually have multiple budget types in additional tables so if I add another budget table the values get doubled where there is another budget in the second budget table. So this solution is ok for one "budgets" table.

ScaisEdge Over a year ago

anyway each aggreagation function return the same result ....sum()... max() ...min().... ... the concept is the same

Klaas Weerstand · Accepted Answer · 2019-11-15 12:01:02Z

0

probably this is what you wanted to know.

SQL - Select rows from two different tables

use outer join

INNER JOIN budgets AS b ON a.id = b.company_id

to

outer join budgets AS b ON a.id = b.comapny_id

answered Nov 15, 2019 at 12:01

Klaas Weerstand

11612 bronze badges

Collectives™ on Stack Overflow

mysql result in two columns from same table without duplicate rows

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related