Left Join Count

Anyone know why I'm getting over 1000 for a count when there's only a possible 40 matches?

Thanks for reading.

 

Without seeing the contents of the tables, no. But you can try other join modes to see if the count changes? My first guess would be that either of those tables have multiple matches for the profile.username = users.username.

 

Well user table is basic user stuff, and profile is basic profile stuff.

user: id,username, email, password, passwordHash, dob.
profile: id,username, height, weight, ect

there are only about 45 entries in both. One is the same user duplicated in both tables, so matching data.

Like say I do 'select `username` from users where `username` = 'Janice'

I will get 40 entries. I would think my left join count would count those 40 matches, not return 1993 as a value, lol.

What do you mean try other left join combinations? Any precise examples?

Thanks again for reading

 

DISTINCT works as a bandaid. Will work in final version, but not sure why count itself isn't

 

Wait... you say you get 40 returns when you do this: 'select `username` from users where `username` = 'Janice'

Why do you get 40 rows returned when you ask for one user by name? Shouldn't that return _1_ row? If you've done what I think you've done, ie duplicated a name or something over all 40 iterations, remember that EVERY match is counted for EACH TIME it matches. (If you have two Janice in the first table, and you match that with 40 entries in the second table, you will get 80 resulting rows, and so on).

Match on ID instead of username, perhaps.

 

Sorry, I'm not sure what I was typing. I had 40 entries, but my count was coming back in the thousands. I had to count DISTINCT for some reason, which will work fine. Just not sure why I was getting 1000 some as a count when there were 40 entries. All good, just not understanding why DISTINCT was needed, and it couldn't just count the right amount of duplicate entries, lol.

 

Because of your LEFT OUTER JOIN, your query just counts the product of your 2 tables : 40 X 40.
I think you should use a INNER JOIN or use a query like this one :

SELECT count(profile.username)
FROM profile,
users
WHERE profile.username=users.username
AND users.country = ?
AND users.province = ?
AND users.city = ?
AND profile.age <= ?

 

Without going into much detail within the context of this post, usually this is exactly the correct behavior of LEFT JOIN.

To ellaborate:
If the table you are using on the left side of the query has N unique rows (let's say N users) and you are joining to a table (let's say user's "comments" in a blog), joining by the User ID column (let's assume each user has Y comments each), you will receive X*Y results in the final query.

In other words, one row for each unique user + comment combination.

If you have a real example, please provide a SQL fiddle for it (e.g. on sqlfiddle.com) so we can provide a more clear solution.

Also, please check this very detailed article on MySQL joins. It's a good know-how for any web developer (even one who does not tackle MySQL on a day to day basis).