Using case when counting results from another table

Databases
1

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!

2

why does the testimonials query have to be combined with the searches query?

how are the two tables related?

3

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.

4

no, not at all

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;
5

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?

6

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