SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query to filter out certain people

    Here is my query to view people:

    Code:
    SELECT ID, date, firstName, lastName, state, country, phone, available, needs, modified FROM contacts where label = 'Yes' and interest <> 'None' and joined is null and (modified <> '$today' or modified is null) and (phone is not null and phone <> '999-999-9999' and phone <> '') and country = 'United States' and (DATE_SUB(CURDATE(),INTERVAL 21 DAY) >= date) order by $sort
    Here is my count query to find out how many times a person has been called. I don't want to show people on the results page if they have been called more than 5 times already.

    Code:
    select count(*) as total from communication where type = 'Calls' and person = '$ID'
    How can I blend these into one query so that people who have been called 5 or more times won't even show up in my results?

    Thanks!!

  2. #2
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The answer to your question depends upon your underlying database. For instance, if you were using MS-Access or MS-SQL I'd first create a stored query/view called "CountOfCommunication" that looked something like this:

    Code:
    select count(*) as total from communication WHERE  type = 'Calls' Group BY person
    Then I'd modify your other query such that it had a Left outer join to the query just created and add "total<=5" to your where statment.

    The latest version of MySQL that is still in beta has a simular capability, but I don't understand how to use. The version of MySQL that most hosting companies have is the non-beta version of MySQL that does not support compound queries. In which you would have to rely upon a very non-elegant method to achive your goal using PHP rather than MySQL queries. How I built the PHP routine would depend upon how many contacts you had in your database, however, it would probably entail the use of an array. I mention PHP, because of the PHP in the syntax of the query you provided.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    f.y.i. kenneth, version 4.1 went into production status about a month or so ago

    however, this problem does not require a subquery, it's a simple join
    Code:
    select con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
      from contacts as con
    left outer
      join communication as com
        on con.ID = com.person
     where con.label = 'Yes' 
       and con.interest <> 'None' 
       and con.joined is null 
       and ( con.modified <> '$today' 
          or con.modified is null ) 
       and ( con.phone is not null 
         and con.phone <> '999-999-9999' 
         and con.phone <> '' ) 
       and con.country = 'United States' 
       and date_sub(curdate(),interval 21 day) 
        >= con.date   
    group
        by con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
    having count(*) <= 5
    order 
        by $sort
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good to hear that 4.1 came out of beta. Maybe my web host will upgrade to it in the near future.

    One caveat, in regards to the query provided by r937, most databases can not group on any memo/longtext fields, so this might cause a problem. The solution would be to convert these fields to a varchar with 255 characters or eliminate such fields from the query.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    to avoid the problem with grouping on a text/memo field, here is the correlated subquery version of the query:
    Code:
    select ID
         , date
         , firstName
         , lastName
         , state
         , country
         , phone
         , available
         , needs
         , modified 
      from contacts as con
     where label = 'Yes' 
       and interest <> 'None' 
       and joined is null 
       and ( modified <> '$today' 
          or modified is null ) 
       and ( phone is not null 
         and phone <> '999-999-9999' 
         and phone <> '' ) 
       and country = 'United States' 
       and date_sub(curdate(),interval 21 day) 
        >= date  
       and 5 >=
           ( select count(*)
               from communication 
              where person = con.ID )
    order 
        by $sort
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I better understand MySQL's subquery, but how would link to a sub query that output multiple fields? For example say I wanted to nest the following table with the following fields

    SELECT fkTable1ID, T2F1, T2F2 FROM tblTable2

    within

    SELECT Table1ID, T1F1, T1F2 FROM tblTable1

    where I'd be linking fkTable1ID to Table1ID
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    link a subquery?

    i think you must be thinking of a join again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    link a subquery?

    i think you must be thinking of a join again
    Yes that is what I ment just a poor choice of words. I've been trying to read up about this, but all the examples I've found try to throw too much detritus into their examples so I can't understand the nuts and bolts of making a join to a nested query. Hence I gave two simple queries to use in an example.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here is the above query written with a scalar subquery in the SELECT list:
    Code:
    select ID
         , columns
         , ( select count(*)
               from communication 
              where person = con.ID ) as total
      from contacts as con
     where conditions
       and 5 >= total
    here is the above query written with a derived table subquery in the FROM clause:
    Code:
    select ID
         , columns
         , total
      from contacts as con
    inner
      join ( select person
                  , count(*) as total
               from communication 
             group
                 by person 
           ) as dt
        on con.ID = dt.person
     where conditions
       and 5 >= total
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I understand, thank you.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, in post #3 above, I have taken your query and plugged it in. It works fine, so I guess that it works even when my server is running mySQL 4.0.22 and not 4.1 The next question I have for you is where would I insert into the query something about com.type = 'Calls' ? I see where you have, "having count(*) <= 5" but what specifically is this counting? I need for it to count up all of the entries in the communication table where the type is "Calls". Does this make sense? I appreciate your help!!

    Thanks!

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    put and com.type='Calls' between on con.ID = com.person and where con.label='Yes'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I ran a few tests and this works great for calls that are greater than one. However, when I put count(*) < 1, for the people who have never received any calls, it returns zero results. This is even though I know there are tons of people in the database that have never been called. Am I doing something wrong?

    Thanks!

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, well, you see, with an inner join, you're always going to get at least one matching row

    if you want no matching rows, change inner to left outer, and then, realizing that there might be no results from the derived table, you cannot expect total to be 0, so you have to check for dt.person is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, this works perfectly. I can now run a report and show the people where conversations is zero. (I renamed calls to conversations). One last question. What if I also wanted to add additional filters? For example, show all people where conversations is zero and (messages <6 or attempts <6) Conversations, Messages & Attempts are all options for the "type" column. Here is my code as it currently stands:

    Code:
    $query = "select con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
      from contacts as con
    left outer
      join communication as com
        on con.ID = com.person
       and com.type='Conversation'
     where con.label = 'Yes' 
       and con.interest <> 'None' 
       and con.joined is null 
       and ( con.modified <> '$today' 
          or con.modified is null ) 
       and ( con.phone is not null 
         and con.phone <> '999-999-9999' 
         and con.phone <> '' ) 
       and con.country = 'United States' 
       and date_sub(curdate(),interval 21 day) 
        >= con.date   
       and com.person is null
    group
        by con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
    order 
        by $sort ";}
    Thanks for your help!

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
      from contacts as con
    left outer
      join communication as com
        on con.ID = com.person
     where con.label = 'Yes' 
       and con.interest <> 'None' 
       and con.joined is null 
       and ( con.modified <> '$today' 
          or con.modified is null ) 
       and ( con.phone is not null 
         and con.phone <> '999-999-9999' 
         and con.phone <> '' ) 
       and con.country = 'United States' 
       and date_sub(curdate(),interval 21 day) 
        >= con.date   
    group
        by con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
    having sum(case when com.type='Conversation'
                    then 1 else 0 end) = 0
       and (
           sum(case when com.type='Messages'
                    then 1 else 0 end) < 6
        or sum(case when com.type='Attempts'
                    then 1 else 0 end) < 6
           )
    order 
        by $sort
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937,

    Forgive me for taking so long to get back to you. I implemented your suggested code and unfortunately, it does not work. The only change I made was that I put in 'Message' and 'Attempt' as I don't store the plural use of those words. I have a user who has Attempt = 6 in the database right now. She is not being filtered out. Did I do something wrong?

    Thanks again, we're almost there.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i have no idea

    i'd need to see your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, here is what I currently have:

    Code:
    if ($type == "neverPhoned") {
    
    $query = "
    select con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
      from contacts as con
    left outer
      join communication as com
        on con.ID = com.person
     where con.label = 'Yes' 
       and con.interest <> 'None' 
       and con.joined is null 
       and ( con.modified <> '$today' 
          or con.modified is null ) 
       and ( con.phone is not null 
         and con.phone <> '999-999-9999' 
         and con.phone <> '' ) 
       and date_sub(curdate(),interval 21 day) 
        >= con.date   
    group
        by con.ID
         , con.date
         , con.firstName
         , con.lastName
         , con.state
         , con.country
         , con.phone
         , con.available
         , con.needs
         , con.modified 
    having sum(case when com.type='Conversation'
                    then 1 else 0 end) = 0
       and (
           sum(case when com.type='Message'
                    then 1 else 0 end) < 6
        or sum(case when com.type='Attempt'
                    then 1 else 0 end) < 6
           )
    order 
        by $sort";}

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, that's your query, not your data

    i don't know why the query isn't working without seeing your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just so I can get exactly what you need, when you ask for data, are you referring to the results of this current query? Or the full .php page that this query resides on? Sorry, I'm still kinda a beginner with all this. I do appreciate your willingness to help.

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    I have a user who has Attempt = 6 in the database right now. She is not being filtered out.
    show me the row of the contacts table that contains the person that is not being filtered out, and show me the rows of the communications table that belong to this person
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here are the details of the person who is NOT being filtered out:

    Code:
     ID  	 date  	 confirmed  	 approved  	 label  	 googleAd  	 searched  	 referURL  	 firstName  	 lastName  	 address1  	 address2  	 city  	 state  	 zip  	 country  	 email  	 status  	 egroup  	 enjoys  	 phone  	 available  	 format  	 heard  	 prices  	 purchased  	 propay  	 DVD  	 opportunity  	 career  	 followup  	 needs  	 interest  	 card1  	 card2  	 card3  	 card4  	 card5  	 card6  	 firstresponse  	 joined  	 membertype  	 ordered  	 modified
    241 	2003-10-18 	Yes 	Yes 	Yes 	MassageOil 	NULL 	NULL 	Donna 	Cullum 	151 Three Mile Harbor 	  	East Hampton 	NJ 	11937 	United States 	donnacullum@test.net 	Valid 	Yes 	NULL 	911-911-7480 	Anytime 	CD 	No 	Prices only 	NULL 	NULL 	NULL 	  	Massage therapist 	  	  	Low 	2003-10-28 	2003-11-04 	2003-11-11 	2003-11-19 	2003-11-28 	2003-12-15 	NULL 	NULL 	NULL 	NULL 	2004-12-11
    Here are the rows in the communication table for the same person. Thanks for your help. By the way, what is your first name?

    Code:
     cID  	 date  	 person  	 type  	 text
    1235 	2003-10-18 00:00:00 	241 	Initial 	I am a massage therapist and esthetician
    2168 	2004-02-21 00:00:00 	241 	Note 	        Updated phone via online search.
    2169 	2004-03-04 00:00:00 	241 	Egroup 	        The egroup sent me a message saying her email addr...
    2170 	2004-04-17 00:00:00 	241 	Attempt 	Talked to daughter.
    3086 	2004-12-04 23:47:00 	241 	Attempt 	Asked her to call.
    3190 	2004-12-11 19:48:00 	241 	Attempt 	No one home.
    3307 	2005-01-02 00:00:00 	241 	Attempt 	this is my test
    3308 	2005-01-02 00:00:00 	241 	Attempt 	One of my other tests.
    3309 	2005-01-02 00:00:00 	241 	Attempt 	The final attempt that should get her blocked.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in post #15 you asked "For example, show all people where conversations is zero and (messages <6 or attempts <6)"

    in the sample data, attempts is 6 but messages is 0

    therefore the OR is true, so the person is selected
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ahhh, that makes perfect sense. So to fix it, I would just need to change the "OR" to "AND", correct? Thinking on this a bit further, I'm wondering if I'm giving the people to many chances before I weed them out. How would I modfiy your code so that it counts both "Attempt" and "Message" and as long as their combined total is less than six, then don't filter them out? Because the more I think about it, 6 phone calls, regardless if I leave a message or not, should be a sufficient number of followup calls before I toss the person.

    Thanks r937!


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
  •