Need help on an advanced query

Databases
1

This query works great. It shows me all the users that have leads but have not upgraded to the Gold subscription to actually contact those leads. Recently I’ve added another table called, campaigns. When the user logs in I ask them if they would like to upgrade to Gold. We store their answer in the campaigns table. So I need to tweak the below query to take into account that a particular user may have said, “No” when asked if they wanted to upgrade to Gold. If they did say No, then we want to exclude them from the query results. Can someone help me here?

Thanks!

SELECT users.guide
, guides.uID
, date_format(guides.last, ‘%m-%d-%Y’) as last
, guides.firstname
, guides.lastname
, guides.visited
, guides.subscription
, COUNT(*) as total
FROM users
INNER
JOIN users as guides
ON guides.uid = users.guide
WHERE
guides.subscription <> ‘Gold’
GROUP
BY guide
ORDER
BY total DESC;

2

If users do not have an entry in the campaigns table until they say ‘NO’ then you’ll have to use a LEFT OUTER JOIN.

3

They actually may have an entry in the campaigns table other than, “No”. There could be a “Thinking” or “Busy”. If users indicate these answers when they login, they we hit them up the next time they come back. Can you help?

Thanks!

4

users? guides? leads?

if you want help on a query that involves stuff we never heard of, it usually helps to explain that stuff, so we can get an idea of how the query is supposed to work

a SHOW CREATE TABLE for each table involved will go a long way

5

Ok here is the campaigns table:

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=3887 DEFAULT CHARSET=latin1

Here is the users table:

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=28147 DEFAULT CHARSET=latin1

6

you store either ‘Yes’ or ‘No’ in the answer column of the campaigns table?

if so, the query will be easy

by the way, TEXT is for large blocks of, um, text, like, say, a chapter in a book – its size goes up to 65K

yes/no can be recorded in a CHAR(2) column

7

oh, and i just noticed, a campaign can have only one user?

that doesn’t make sense to me, so obviously i still don’t understand your tables

8

Not just yes or no

9

Still there Rudy?

10

yeah, sorry about the delay

you still didn’t clear up whether you’re looking at a user or a guide, so i’m not really sure who it is that is supposed to have said “No” here…

but at least i can tell you the technique involved and let you take it from there

it’s called LEFT OUTER JOIN with an IS NULL check

otherwise known as returning unmatched rows

basically, you set the LEFT OUTER JOIN up to look for the rows you ~don’t~ want to find (this is the part that traps most people), and then use IS NULL to make sure you didn’t find it

SELECT users.guide
     , guides.uID
     , DATE_FORMAT(guides.last,'%m-%d-%Y') AS last
     , guides.firstname
     , guides.lastname
     , guides.visited
     , guides.subscription
     , COUNT(*) AS total 
  FROM users 
INNER
  JOIN users AS guides
    ON guides.uid = users.guide
   AND guides.subscription <> 'Gold'
LEFT OUTER
  JOIN campaigns
    ON campaigns.uID = guides.uID -- not sure about this part
   AND campaigns.answer = 'No'
 WHERE campaigns.uID IS NULL -- didn't find it
GROUP 
    BY users.guide
ORDER 
    BY total DESC;
11

Rudy, this query is working perfectly. Where do I add “where total > 1” ? I need a way to filter out those who only have one lead, otherwise my report is way too long to go through.

Thanks!!

12

not WHERE, but HAVING :slight_smile: