CODEWITHSUNDEEP
mysqlsql
GabAntonelli
GabAntonelli

Reputation: 767

Issue joining multiple tables mysql

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

Upvotes: 0

Views: 338

Answers (1)

Hart CO
Hart CO

Reputation: 34774

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

  1. If counts are too high and you have JOIN's then it's likely insufficient JOIN criteria, IE Cartesian product.
  2. If counts are too low, you're likely excluding records you don't mean to through WHERE criteria or restrictive JOIN criteria.
  3. 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.

Upvotes: 1

Related Questions