1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Left Join Count

Discussion in 'MySQL' started by Jeremy Benson, Dec 30, 2016.

  1. #1
    Anyone know why I'm getting over 1000 for a count when there's only a possible 40 matches?
    SEMrush
    Thanks for reading.
     
    Jeremy Benson, Dec 30, 2016 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,626
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #2
    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.
     
    PoPSiCLe, Dec 30, 2016 IP
  3. Jeremy Benson

    Jeremy Benson Active Member

    Messages:
    337
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    73
    #3
    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 :)
     
    Jeremy Benson, Dec 30, 2016 IP
  4. Jeremy Benson

    Jeremy Benson Active Member

    Messages:
    337
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    73
    #4
    DISTINCT works as a bandaid. Will work in final version, but not sure why count itself isn't
     
    Jeremy Benson, Dec 30, 2016 IP
  5. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,626
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #5
    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.
     
    PoPSiCLe, Dec 30, 2016 IP
    sarahk likes this.
  6. Jeremy Benson

    Jeremy Benson Active Member

    Messages:
    337
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    73
    #6
    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.
     
    Jeremy Benson, Jan 7, 2017 IP
  7. Zoidrums

    Zoidrums Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    11
    #7
    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 <= ?
     
    Zoidrums, Feb 11, 2017 IP
  8. ColorWP.com

    ColorWP.com Notable Member

    Messages:
    3,121
    Likes Received:
    100
    Best Answers:
    1
    Trophy Points:
    270
    #8
    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).
     
    ColorWP.com, Mar 5, 2017 IP