I've got a problem with a MySQL query.

Suppose we have two tables:

cars:

car_debts:

I want to produce a result like this:

So, basically querying the result takes into account the "fl_unique" flag. If car_debts has "fl_unique" as true in some row, only the value with the flag should be considered, the other values ​​need to be 0.0. If they don't have the flag as true, nothing changes in the values.

This is a simple abstraction of my problem. Thanks for any help or tips!

CodePudding user response：

The following steps can be found on this example.

First start by finding which distinct car_id have fl_unique true (0), this can be achieved by using :

select car_id
from car_debts
group by car_id
having SUM(fl_unique <> 0)>0 

The we can use join and case expression as follows:

select cd.id,
       case when tbl.car_id is null then cd.value 
            when cd.fl_unique = 1 and tbl.car_id is not null then cd.value
            when fl_unique=0  and tbl.car_id is not null then  0.0 end as value,
       c.id,
       cd.fl_unique,
       c.brand,
       tbl.car_id
from car_debts cd
left join (select car_id
           from car_debts
           group by car_id
           having SUM(fl_unique <> 0)>0 
           ) tbl on tbl.car_id=cd.car_id
inner join cars c on c.id=cd.car_id;

Note The left join with the subquery helps to create the case conditions .

If all group values of car_id (in your example 2) are null then we select value as are

CodePudding user response：

Use MAX() window function to get the max value of fl_unique for each car_id and compare it to the current value of fl_unique.
If they are not the same this means that the returned value must be 0, else value:

SELECT d.id,
       (MAX(d.fl_unique) OVER (PARTITION BY d.car_id) = d.fl_unique) * d.value AS value,
       d.car_id, 
       d.fl_unique,
       c.brand
FROM car_debts d INNER JOIN cars c
ON c.id = d.car_id;

See the demo.