Question 21208 in Mysql Question posted in
The official MySQL documentation can be found here.

Join a table with condition based on values with "flags" – Mysql

I’ve got a problem with a MySQL query.

Suppose we have two tables:

cars:

id brand owner_id
1 toyota 24
2 ford 56
… … …

car_debts:

id value car_id(fk) fl_unique(boolean)
24 50.0 1 0
25 40.0 1 0
26 90.0 1 1
27 10.0 2 0
28 20.0 2 0
29 30.0 2 0
… … … …

I want to produce a result like this:

car_debts.id car_debts.values cars.id car_debts.fl_unique(boolean) cars.brand
24 0.0 1 0 toyota
25 0.0 1 0 toyota
26 90.0 1 1 toyota
27 10.0 2 0 ford
28 20.0 2 0 ford
29 30.0 2 0 ford
… … … … …

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!

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search