Issue joining multiple tables mysql

Question

SELECT    taxi.id as id,name,
  SUM(storico_pagamenti.importo) AS pagamentitotali,
  SUM(CASE WHEN storico_pagamenti.data <= '$timestop' AND storico_pagamenti.data >=     '$timestart' THEN storico_pagamenti.importo ELSE 0 END) AS pagamentimese,
  SUM(storico_fatture.importo) AS fatturetotali,
  SUM(CASE WHEN storico_fatture.data <= '$timestop' AND storico_fatture.data >= '$timestart' THEN storico_fatture.importo ELSE 0 END) AS fatturemese,
  COUNT(giornifermi.registrazione) as giornifermitotali,
  SUM(CASE WHEN giornifermi.data <= '$timestop' AND giornifermi.data >= '$timestart' THEN 1 ELSE 0 END) AS giornifermimese
FROM taxi 
  LEFT JOIN storico_pagamenti ON taxi.id = storico_pagamenti.id_taxi
  LEFT JOIN storico_fatture ON storico_pagamenti.id_taxi=storico_fatture.id_taxi
  LEFT JOIN giornifermi ON storico_fatture.id_taxi=giornifermi.id_taxi
WHERE taxi.categoria='cv'
GROUP BY  taxi.id;

My mysql skills aren't enough for this query... can't understand where is the problem. The part that is not working is this

SUM(CASE WHEN giornifermi.data <= '$timestop' AND giornifermi.data >= '$timestart' THEN 1
ELSE 0 END) AS giornifermimese

which I expected to give me the count of the rows of giornifermi.data between two dates submitted

and this other part:

COUNT(giornifermi.registrazione) as giornifermitotali

which I expected to give me the count of all the rows in giornifermi.registrazione column

Is this because of the join? Or is it a problem in the two parts of the query?

EDIT----------------------------------------- I've related now all the tables "ON" the same referrer taxi.id setting it equal to the variable id_taxi of the other tables and now I get the values but wrong. After the suggestion of @Twelf I've tried to comment the "GROUP BY taxi.id" and try select *. I've seen the problem now is that results are multiplied. Example If i have three rows in giornifermi, they became 6, if i have 2 rows in storico_pagamenti, It copies me again the three rows for each row in storico_pagamenti. in Don't have any idea of how to fix that but knowing the problem is better than nothing

EDIT------------------------------------------ Fixed by adding a subquery after the left join, instead of joining the whole table of giornifermi i've created there a brand new table to correct the data without duplicates

LEFT JOIN (SELECT 
taxi.id as i,
COUNT(giornifermi.registrazione) as giornifermitotali,
SUM(CASE
    WHEN giornifermi.data <= '$timestop' AND giornifermi.data >= '$timestart' THEN 1
        ELSE 0
    END) as giornifermimese
FROM taxi
LEFT JOIN giornifermi ON taxi.id=giornifermi.id_taxi
GROUP BY taxi.id
) AS NUOVATABELLA ON taxi.id=NUOVATABELLA.i

What are the expected results and how do they compare with present results? — Mihai
– Mihai, Commented Sep 12, 2013 at 16:52
it doesn't give me the result expected, the count of the rows is always wrong — GabAntonelli
– GabAntonelli, Commented Sep 12, 2013 at 16:54
This is going to be tough to determine why your data is wrong since it is fine syntax-wise and we really don't know what your tables mean. — Scotch
– Scotch, Commented Sep 12, 2013 at 16:55
now the are all 0, before i was trying to relate all the join to the first table like LEFT JOIN storico_pagamenti ON taxi.id = storico_pagamenti.id_taxi LEFT JOIN storico_fatture ON taxi.id=storico_fatture.id_taxi LEFT JOIN giornifermi ON taxi.id=giornifermi.id_taxi and I got some numbers but wrong... — GabAntonelli
– GabAntonelli, Commented Sep 12, 2013 at 17:01

Hart CO · Accepted Answer · 2013-09-12 22:35:57Z

1

It's impossible to tell from your question, but basic trouble-shooting for 'wrong' counts:

If counts are too high and you have JOIN's then it's likely insufficient JOIN criteria, IE Cartesian product.
If counts are too low, you're likely excluding records you don't mean to through WHERE criteria or restrictive JOIN criteria.
Select all fields for a small subset of records to help see where the discrepancy is coming from.

Update:

One or more of your tables has multiple entries for all/some taxi_ID values.

Run this for each table:

SELECT taxi_ID 
FROM storico_pagamenti 
GROUP BY taxi_ID HAVING COUNT(*) > 1

If any tables return records, look at them to see if there's another field you should be using in the JOIN to differentiate between the duplicates.

edited Sep 12, 2013 at 22:35

answered Sep 12, 2013 at 17:10

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

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

5 Comments

GabAntonelli Over a year ago

I've related all the tables "ON" the same referrer taxi.id and now I get the values but wrong. After the suggestion of @Twelf I've tried to comment the "GROUP BY taxi.id" and try select *. I've seen the problem now is that results are multiplied. Example If i have three rows in giornifermi, they became 6, if i have 2 rows in storico_pagamenti, It copies me again the three rows for each row in storico_pagamenti. in Don't have any idea of how to fix that but knowing the problem is better than nothing

Hart CO Over a year ago

Updated answer, should be able to find out which table is problematic easily.

GabAntonelli Over a year ago

what do you mean? of course I have a unique key for each record in those tables, but how can I add them to the join? the unique key column is call 'registrazione'

Hart CO Over a year ago

You just want to isolate which tables are resulting in duplicates, one of your tables has multiple records for the same taxi_ID, you need to handle those, you can make a subquery for the offending table and group by taxi_ID, or you can add to your JOIN criteria so that only one record joins between each table. Without seeing data I can't know which you need to do.

GabAntonelli Over a year ago

i fixed it creating a subquery after the left join to create a brand new table to join. LEFT JOIN (SELECT taxi.id as i, COUNT(giornifermi.registrazione) as giornifermitotali, SUM(CASE WHEN giornifermi.data <= '$timestop' AND giornifermi.data >= '$timestart' THEN 1 ELSE 0 END) as giornifermimese FROM taxi LEFT JOIN giornifermi ON taxi.id=giornifermi.id_taxi GROUP BY taxi.id ) AS NUOVATABELLA ON taxi.id=NUOVATABELLA.i

Collectives™ on Stack Overflow

Issue joining multiple tables mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related