CODEWITHSUNDEEP
mysql
sunsa428
sunsa428

Reputation: 147

MySQL: using Case When Then End for multiple Inner Join tables

I have a query which returns results using 'Case When Then End' from three tables which are joind. It looks like this:

select f.filename, 
sum(case when v.rUser like '%bike%' then 1 else 0 end) as bikeUser, 
sum(case when v.rUser like '%Pedestrian%' then 1 else 0 end) as pedestrianUser, 
sum(case when d.weather like '%clear%' then 1 else 0 end) as clearWeather
from VMdata v 
inner join files f on v.id = f.id
inner join DMdata d on f.id = d.id
where f.filename in (X,Y,Z)
group by f.filename

This works fine, with each resulting-row giving eaither 1 or 0 correctly. The thing to note here is that each table has only one entry(row) for a specific "filename". Now when I try to add another Inner Join with a table which can have multiple entries(rows) per "filename", the result becomes wrong in a way that only the last 'sum' shows the correct values whereas other 'sums' give wrong values. This second query is:

select f.filename, 
sum(case when v.rUser like '%bike%' then 1 else 0 end) as bikeUser,
sum(case when v.rUser like '%Pedestrian%' then 1 else 0 end) as pedestrianUser, 
sum(case when d.weather like '%clear%' then 1 else 0 end) as clearWeather,
sum(case when m.extras like '%hat%' then 1 else 0 end) as hatExtras
from VMdata v 
inner join files f on v.id = f.id
inner join DMdata d on f.id = d.id
inner join MultiFiledata m on f.id = m.id
where f.filename in (X,Y,Z)
group by f.filename

Any idea to get the right figures for all columns?

Upvotes: 2

Views: 2520

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169484

If the MultiFiledata table can contain multiple records for any corresponding record in the files table, then you'll need to do the aggregate in a separate query and join that back to the main query.

For example (syntax may not be perfect; concentrate on concept):

select   f.filename, 
         sum(case when v.rUser like '%bike%' 
                  then 1 else 0 end) as bikeUser,
         sum(case when v.rUser like '%Pedestrian%' 
                  then 1 else 0 end) as pedestrianUser, 
         sum(case when d.weather like '%clear%' 
                  then 1 else 0 end) as clearWeather
from     VMdata v 
         inner join files f 
         on v.id = f.id
         inner join DMdata d 
         on f.id = d.id
         inner join (
             select id, 
                    sum(case when extras like '%hat%' 
                             then 1 else 0 end) as hatExtras
             from   MultiFiledata 
         ) m 
         on f.id = m.id
where    f.filename in (X,Y,Z)
group by f.filename;

Upvotes: 1

davesnitty
davesnitty

Reputation: 1860

Your one-to-many join is causing the overall row count to increase, such that when your value=1, you are adding that value more than once. To mitigate this, you may want to use the count function instead, using it to count distinct user IDs. Something like:

Count(distinct case when [logic goes here] then [user ID] else null end) as bikeuser

Then, you are only counting each user once rather than adding each row.

Upvotes: 0

Related Questions