Trouble joining users and campaigns tables

[busboy]

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;

[r937]

you have to use LEFT OUTER JOINs in both cases

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;

[busboy]

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!

[r937]

do your tables have the proper indexes?

do a SHOW CREATE TABLE for each one to see...

[busboy]

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

[r937]

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

[busboy]

Thanks boss! This did the trick!