SitePoint Sponsor

User Tag List

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

    Join is not producing the right results

    Rudy,

    Can you help me with this query? It's pulling the most recent answer date in the campaigns table for each user, but not the correct associated answer.

    Thanks!



    Code:
    				SELECT date_format(u.registerDate, '%m-%d-%Y') as registerDate
    				    , u.uID
    				    , u.firstName
    				    , u.lastName
    				    , u.memberType
    				    , u.logins
    				    , date_format(u.lastLogin, '%m-%d-%Y') as lastLoginFormatted
    				    , date_format(c.latest, '%m-%d-%Y') as answerDate
    				    , c.campaign
    				    , c.answer
    				FROM users u
    				LEFT 
    				  JOIN (SELECT uID, MAX(answerDate) AS latest, campaign, answer FROM campaigns where campaign = 'Enrollment' GROUP BY uID) AS c
    				    ON c.uID = u.uID
    				where u.sponsorID = '110' 
    					and u.memberType = 'No'
    					and answer != 'Rejected'
    					and u.lastName = 'Laib'
    				order by lastLogin desc;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    how kind of you to ask for me by name, but i'm sure there are others out there that could've done this one as well
    Code:
    SELECT DATE_FORMAT(u.registerDate, '%m-%d-%Y') AS registerDate
         , u.uID
         , u.firstName
         , u.lastName
         , u.memberType
         , u.logins
         , DATE_FORMAT(u.lastLogin, '%m-%d-%Y') AS lastLoginFormatted
         , DATE_FORMAT(c.latest, '%m-%d-%Y') AS answerDate
         , c.campaign
         , c.answer
      FROM users AS u
    LEFT 
      JOIN ( SELECT uID
                  , MAX(answerDate) AS latest
               FROM campaigns 
              WHERE campaign = 'Enrollment'
                AND answer <> 'Rejected'
             GROUP 
                 BY uID ) AS c
        ON c.uID = u.uID
    LEFT OUTER
      JOIN campaigns
        ON campaigns.uID = c.uID
       AND campaigns.answerDate = c.latest
     WHERE u.sponsorID = '110' 
       AND u.memberType = 'No'
       AND u.lastName = 'Laib'
    ORDER 
        BY lastLogin DESC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm now getting the following error:

    Unknown column 'c.campaign' in 'field list'
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oops, sorry, c.campaign and c.answer in the SELECT clause should be campaign.campaign and campaign.answer, although to be honest you don't need to return campaign.campaign because it's gonna say 'Enrollment'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy!!
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    oops, sorry, c.campaign and c.answer in the SELECT clause should be campaign.campaign and campaign.answer, although to be honest you don't need to return campaign.campaign because it's gonna say 'Enrollment'
    UH OH!

    Question: Do you want to be including nulls in the sub query?

    Code MySQL:
    LEFT JOIN (
    SELECT uID , MAX(answerDate) AS latest
    FROM campaigns
    WHERE campaign = 'Enrollment' AND answer <> 'Rejected' -- believe this will exclude if 'answer' is null 
    GROUP BY uID
    ) AS c

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I do not want to include nulls in this case. Thank you for following up.
    Convert your dollars into silver coins. www.convert2silver.com


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
  •