Double Count() With Many Join Returns Wrong Values

[FedericoBiccheddu]

I have a problem that keeps me going in a fairly ambitious project.I have the following query:

SELECT b.band_id, b.band_name, b.band_style, b.band_type, b.band_status, b.band_label, b.band_formed, b.band_disbanded, b.band_nation, b.band_city, b.band_website, b.band_myspace, b.band_yt, b.band_tribute, b.band_add, b.band_add_time, b.band_other_names, LOWER(LEFT(b.band_name, 1)) AS band_letter, COUNT(a.album_id) AS albums, COUNT(f.fan_band) AS fans, fu.fan_id, u.username, u.user_colourFROM bands b	LEFT JOIN bands_fans f		ON (f.fan_band = b.band_id)	LEFT JOIN bands_fans fu		ON (fu.fan_id = 2)	LEFT JOIN users u		ON (u.user_id = b.band_add)	LEFT JOIN bands_albums a		ON (a.album_band = b.band_id)WHERE b.band_id = 1

The two COUNT() but always return the same value! If I delete COUNT(a.album_id) AS albums and LEFT JOIN bands_albums a ON (a.album_band = b.band_id), the query returns the correct value.Thanks in advance

[justsomeguy]

Normally when you use a function like COUNT you would also have a GROUP BY clause. Look into using GROUP BY and see if that helps.

[FedericoBiccheddu]

I do not understand why eliminating a JOIN works! What index should I use? Fan or Album or the main table (Band)?

[justsomeguy]

I do not understand why eliminating a JOIN works!

It's a little hard to explain, which may mean I don't fully understand it myself, but here goes:When you do a join you create a combination of the two tables. It will create as many combinations as possible that satisfy the join conditions. So, if you have 1 record in the band table, and 20 records in the fans table, joining those tables will produce 20 rows. Even if you use a count, you'll get the same row duplicated 20 times, because it's joining for every row in the table. So the count will say 20, because there are 20 rows. If you then join that with another table which has 20 rows in it, now you have 400 rows total. Both counts will say 400 rows then. If you join it with another table with 10 rows, and end up with 4000 rows, and you have 3 counts, all 3 counts will list 4000. You're not telling the counts really what to count, you're just telling it to count all rows, so all counts will be the same number. The GROUP BY clause typically tells it what to group by so that it knows what to count.

[FedericoBiccheddu]

I solved the count of the fans by adding DISTINCT, but I have not solved the problem of the album. I do not understand why I'll be restituta a row from the query, the count 24 when the records are 14.

[justsomeguy]

It's counting all of the joined records, not just the records from that one table. If you run that query you should see that it actually outputs 24 rows, not just 1. It will be the same row duplicated 24 times, but that's where it's getting the count from.