Trouble joining users and campaigns tables

Databases
1

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;

2

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
[COLOR="#0000FF"]LEFT [/COLOR]OUTER
  JOIN ( SELECT uID
              , MAX(answerDate) AS mostRecent
           FROM campaigns
          WHERE campaign = 'Enrollment'
         GROUP
             BY uID ) AS r
    ON r.uID = u.uID
[COLOR="#0000FF"]LEFT [/COLOR]OUTER
  JOIN campaigns c
    ON c.uID = r.uID
   AND c.answerDate = r.mostRecent
 WHERE u.guide = 12481
ORDER
    BY c.answerDate desc;
3

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!

4

do your tables have the proper indexes?

do a SHOW CREATE TABLE for each one to see…

5

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

6

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

7

Thanks boss! This did the trick!