SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Getting results from a double join when I shouldn't be

    Because user 110 already has a row in the campaigns table, I wasn't expecting to get any rows back when executing this query....but I ended up getting a total count of how many guides user 110 has. Did I screw up my double join query?

    Thanks!



    SELECT COUNT(*) as total
    FROM (users INNER JOIN users as guides ON guides.uid = users.guide)
    LEFT OUTER JOIN campaigns
    ON campaigns.uID = users.uID
    AND campaigns.campaign = 'Upgrade to gold'
    AND campaigns.answer IS NULL
    WHERE users.guide = 110;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,084
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    users? guides? campaigns? could we have some context here please?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    users = table
    guide = a column in the users table

    campaigns = table
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,084
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    LEFT OUTER JOIN basically states "If there is a campaign with uID equal to user.id, JOIN it in the row, otherwise put all fields to NULL". Since this is optional, in the grand scheme of things you can neglect it, and your query boils down to

    Code mysql:
     
    SELECT COUNT(*) as total 
    FROM (users INNER JOIN users as guides ON guides.uid = users.guide) 
    WHERE users.guide = 110;

    Which will indeed get you the number of guides for a user.
    What are you trying to achieve?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What I'm trying to achieve is this: If a user has a row in the campaign table for this particular campaign, then don't return anything at all when the query is executed. That way when they login again, they won't be shown the campaign again. Does this help?
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,084
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Yup, it does
    Sounds like you want something like this

    Code mysql:
    SELECT COUNT(*) as total 
    FROM (users INNER JOIN users as guides ON guides.uid = users.guide) 
    LEFT OUTER JOIN campaigns 
    ON campaigns.uID = users.uID 
    AND campaigns.campaign = 'Upgrade to gold' 
    AND campaigns.answer IS NULL
    WHERE users.guide = 110
    AND campaigns.uID NOT IN (
      SELECT uID FROM users
    )
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, I came up with something similar. But both your example and mine produce a single row, called total, where the value is zero. How can we tweak this so that no rows are returned at all if a person has already responded to the campaign?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,084
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You query for the COUNT, and that always returns something (indeed if there is nothing to find the COUNT is zero).
    If you don't want to retrieve any results when there are none, you should query for the actual data instead of COUNT
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •