SitePoint Sponsor

User Tag List

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

    Need help on an advanced query

    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;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    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,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    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)
    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
    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,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    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.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    you store either 'Yes' or 'No' in the answer column of the campaigns table?
    Quote Originally Posted by busboy View Post
    There could be a "Thinking" or "Busy".
    Not just yes or no

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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    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.
    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
    Code:
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!
    Convert your dollars into silver coins. www.convert2silver.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    not WHERE, but HAVING
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •