Konstantin Jahnel Konstantin Jahnel - 2 months ago 7
SQL Question

MySQL: Select Data from Group B with the missing Values from Group A

I need to Group (or otherwise select) data from a database, here a simplified Model of that Database:

user: { id: int, name: varchar }
scenario: { id: int, displayname: varchar, userId: int}
costgroup: { id: int, displayname: varchar }
costgroupvalue: { id: int, scenarioId: int, value: float, year: year, costgroupId: int}


There is one Scenario (Id: 1) with CostGroupValues ranging from 2012 to 2018 and one Scenario (Id: 2) with CostGroupValues ranging from 2016 to 2018.

What I need is: the CostGroupValues for one Scenario (Id: 2) but with the missing CostGroupValues from 2012 to 2015 from the other Scenario (Id: 1).

Here is an example of a query I already did:

I marked the data I don't need in this Query yellow.

SELECT costgroupvalue.*
FROM costgroup
JOIN costgroupvalue ON costgroup.id = costgroupvalue.costgroupid
JOIN scenario ON scenario.id = costgroupvalue.scenarioid
And scenario.userid = '666'
Group by costgroupvalue.id;

Answer

I found a solution on my own:

SELECT costgroupvalue.*
FROM costgroup
JOIN costgroupvalue ON costgroup.id = costgroupvalue.costgroupid
JOIN scenario ON scenario.id = costgroupvalue.scenarioid
AND scenario.userid = '14'
WHERE (scenario.id = 2 AND  costgroupvalue.year between '2016' AND '2018')
Or (scenario.id = 1 AND  costgroupvalue.year between '2012' AND '2015')
Group by costgroupvalue.id
Order by costgroupvalue.costgroupid, costgroupvalue.scenarioid, costgroupvalue.year

This Query gives me the Rows i need: ScenarioId = 1 from 2012 to 2015 and ScenarioId = 2 from 2016 to 2018.

Comments