SitePoint Sponsor

User Tag List

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

    Using case when counting results from another table

    I use this simple query to determine the most popular searches on my website:

    select keyword, count(*) as total from searches group by keyword order by total desc limit 500;

    I want to add a column that indicates how many times a particular keyword is found in testimonials. The testimonials are in a separate table called testimonies. I'm thinking that I need to implement the following, but I don't know exactly where:

    count( case when (t.title like '%s.keyword%' or t.testimonyText like '%s.keyword%') then 1 else null end ) as countInTestimonials

    So how do I modify the searches query to include a count in the testimonies table?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why does the testimonials query have to be combined with the searches query?

    how are the two tables related?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, you're right. I guess they don't have to be combined into one query. So you are suggesting that in my PHP code, in the Do loop, for every keyword that is returned from the first query to then run the second query? To answer your question, the two tables are related in that they are both in the same database.
    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,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    So you are suggesting that in my PHP code, in the Do loop, for every keyword that is returned from the first query to then run the second query?
    no, not at all
    Code:
    SELECT keyword
         , count(*) as total 
         , ( SELECT COUNT(*)
               FROM testimonies 
              WHERE title LIKE CONCAT('%',s.keyword,'%') 
                 OR testimonyText LIKE CONCAT('%',s.keyword,'%') ) AS countInTestimonials
      FROM searches AS s 
    GROUP 
        BY keyword 
    ORDER 
        BY total desc limit 500;
    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)
    This query never finishes. It timed out in the two times I tried to run it. Maybe the 240,000 rows in the searches table and 7,000 rows in the testimonies table is too much?
    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,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Maybe the 240,000 rows in the searches table and 7,000 rows in the testimonies table is too much?
    indeed

    maybe you gotta ask yourself why you want to see both pieces of information at the same time

    also, don't go the route of doing a query inside a loop -- that's gonna be just as slow
    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
  •