CODEWITHSUNDEEP
phpmysql
GabAntonelli
GabAntonelli

Reputation: 767

How to remove empty row of NULL fields from mysql query containing sum

I have this following query which join multiple tables

SELECT targa,
registrazioni.turno as turno,
conduttori.nome as nome,
conduttori.cognome as cognome,
spese_importo,risparmio,km, SUM(spese.spese_importo) AS totspese
FROM registrazioni LEFT JOIN conduttori
ON id_conduttore=conduttori.id
LEFT JOIN spese
ON registrazioni.id=spese.id_registrazione
WHERE dataora='$data';

when I added this SUM(spese.spese_importo), I started having a small issue, and even if the query shouldn't have for me any matching result, I obtain as result an empty row where every field is NULL.

Of course if I remove that SUM from my query, it does work again and I don't have any rows as result of the query

How could I solve it and check if there are results not null?

I've tried via mysql to add the condition

WHERE targa IS NOT NULL

(targa is just one field I've randomly choosen between the different fields they are all NULL)

but it didn't clear the row, and via php I was using the condition

mysqli_num_rows($result)==0

but now that I always have at least 1 row it doesn't work.

any other ideas for checking it before fetching the rows?

Upvotes: 1

Views: 1238

Answers (2)

toph
toph

Reputation: 220

I think you want to group your results. Try to add this at the end of your query :

GROUP BY registrazioni.id

Upvotes: 2

tcchappelear
tcchappelear

Reputation: 24

From http://www.w3schools.com/sql/sql_join_left.asp:

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the >right table (table2). The result is NULL in the right side when there is no match.

Does including sum(spese.spese_importo) create a situation where you would get a match on the left side of the join but not on the right side - and have those NULLs?

Upvotes: 1

Related Questions