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)

    Trouble joining users and campaigns tables

    I have a users table and a campaigns table. I'm trying to show all users where guide = 12481, regardless if they have a response in the campaign table or not. But I'm getting confused with the multiple joins. Can you help?

    Thanks!


    SELECT
    date_format(u.registerDate, '%m-%d-%Y') as registered
    ,u.uID
    ,u.firstname
    ,u.lastname
    ,date_format(u.last, '%m-%d-%Y') as last
    ,u.visited
    ,c.campaign
    ,c.answer
    ,date_format(c.answerDate, '%m-%d-%Y') as latestAnswer
    FROM
    users u
    left
    JOIN
    (SELECT
    uID
    ,MAX(answerDate) mostRecent
    FROM
    campaigns
    WHERE campaign = 'Enrollment'
    GROUP
    BY
    uID) r
    ON
    u.uID = r.uID
    inner
    JOIN
    campaigns c
    ON
    r.uID = c.uID
    AND
    r.mostRecent = c.answerDate
    WHERE
    u.guide = 12481
    ORDER
    BY
    c.answerDate 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,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you have to use LEFT OUTER JOINs in both cases
    Code:
    SELECT DATE_FORMAT(u.registerDate,'%m-%d-%Y') AS registered
         , u.uID
         , u.firstname
         , u.lastname
         , DATE_FORMAT(u.last,'%m-%d-%Y') AS last  
         , u.visited
         , c.campaign     
         , c.answer
         , DATE_FORMAT(c.answerDate,'%m-%d-%Y') AS latestAnswer
      FROM users u
    LEFT OUTER
      JOIN ( SELECT uID
                  , MAX(answerDate) AS mostRecent 
               FROM campaigns
              WHERE campaign = 'Enrollment'
             GROUP
                 BY uID ) AS r
        ON r.uID = u.uID
    LEFT OUTER
      JOIN campaigns c
        ON c.uID = r.uID
       AND c.answerDate = r.mostRecent
     WHERE u.guide = 12481
    ORDER
        BY c.answerDate 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)
    Ok, but now it's throwing this error:

    Error Code: 1104. The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

    Thanks!
    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,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    do your tables have the proper indexes?

    do a SHOW CREATE TABLE for each one to see...
    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)
    CREATE TABLE `campaigns` (
    `cID` int(6) NOT NULL AUTO_INCREMENT,
    `campaign` varchar(20) NOT NULL,
    `answerDate` datetime NOT NULL,
    `uID` int(6) NOT NULL,
    `answer` text NOT NULL,
    PRIMARY KEY (`cID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=9347 DEFAULT CHARSET=latin1


    AND



    CREATE TABLE `users` (
    `uID` int(8) NOT NULL AUTO_INCREMENT,
    `IPaddress` varchar(15) DEFAULT NULL,
    `registerDate` datetime DEFAULT NULL,
    `confirmed` char(3) DEFAULT 'No',
    `confirmProds` int(2) DEFAULT '0',
    `lastConfirmProd` date DEFAULT NULL,
    `postProds` int(2) DEFAULT '0',
    `lastPostProd` date DEFAULT NULL,
    `lazyProds` int(2) DEFAULT '0',
    `lastLazyProd` date DEFAULT NULL,
    `approved` char(3) DEFAULT 'No',
    `visited` int(4) DEFAULT '0',
    `last` datetime DEFAULT NULL,
    `lastTemp` datetime DEFAULT NULL,
    `lastMatch` datetime DEFAULT NULL,
    `searchProds` int(2) DEFAULT '0',
    `lastSearchProd` date DEFAULT NULL,
    `companyName` varchar(50) DEFAULT NULL,
    `companyURL` varchar(50) DEFAULT NULL,
    `firstname` varchar(30) DEFAULT NULL,
    `lastname` varchar(30) DEFAULT NULL,
    `city` varchar(30) DEFAULT NULL,
    `state` varchar(30) DEFAULT NULL,
    `zip` varchar(10) DEFAULT NULL,
    `country` varchar(50) DEFAULT NULL,
    `phone` varchar(15) DEFAULT NULL,
    `password` varchar(30) DEFAULT NULL,
    `reminders` int(2) DEFAULT '0',
    `email` varchar(50) DEFAULT NULL,
    `bouncing` varchar(3) NOT NULL DEFAULT 'No',
    `sendMatches` varchar(3) NOT NULL DEFAULT 'Yes',
    `searchesStopped` int(3) NOT NULL DEFAULT '0',
    `grandFathered` varchar(3) DEFAULT 'No',
    `contact` char(3) DEFAULT 'No',
    `contacted` int(4) DEFAULT '0',
    `skills` varchar(50) DEFAULT NULL,
    `googleTopicID` int(3) DEFAULT NULL,
    `interests` int(2) DEFAULT NULL,
    `member` varchar(11) DEFAULT NULL,
    `membernumber` int(8) DEFAULT NULL,
    `ourDownline` varchar(3) NOT NULL DEFAULT 'No',
    `enrollDate` date DEFAULT NULL,
    `referURL` mediumtext,
    `guide` int(8) DEFAULT NULL,
    `guideUpdated` datetime DEFAULT NULL,
    `totalReferrals` int(4) DEFAULT '0',
    `totalSearches` int(4) DEFAULT '0',
    `totalQuestions` int(4) DEFAULT '0',
    `subscription` varchar(7) DEFAULT NULL,
    `wantSilver` varchar(3) DEFAULT 'No',
    `timeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`uID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=29802 DEFAULT CHARSET=latin1
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    looks like you have only the PKs defined

    ALTER TABLE campaigns
    ADD INDEX x1 (uid,answerDate,campaign)

    then see if the query runs faster and/or the error message goes away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks boss! This did the trick!
    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
  •