I have a table where I record searches performed on my site,
tbl_search. An id is automatically assigned to entries. If the searched phrase is more than one word, my system splits the words and performs another search on each of them, setting a column - called
original - to the original search id.
- A user searches for 'foo bar'
The system searches for 'foo bar', gets the id that was created when recording that search, let's say the id is 5.
So now we have a record like this
id | phrase | results | original ----+----------+---------+---------- 5 | foo bar | 0 | NULL
The system searches for 'foo' and 'bar' by themselves, and records the original id. So now the table looks like this:
id | phrase | results | original ----+----------+---------+---------- 5 | foo bar | 0 | NULL 6 | foo | 0 | 5 7 | bar | 0 | 5
Any phrase with an original value was not specifically searched for, and the results should be grouped, summed, and counted as results for the original search.
I want to know all the searched phrases that got ZERO results, and order them based on how many times they were searched. Here's my SQL:
SELECT s1.phrase, COUNT(s1.phrase) as count, SUM(s2.results) as sub_results FROM tbl_search s1 LEFT OUTER JOIN tbl_search s2 ON s1.id = s2.original WHERE s1.results = 0 GROUP BY s1.phrase, s2.phrase HAVING SUM(s2.results) = 0 OR SUM(s2.results) IS NULL ORDER BY count desc
I'm very close I think, but I'm getting something like this:
phrase | count | results | sub_results ---------+-------+---------+------------ foo bar | 14 | 0 | NULL map | 5 | 0 | 4
I would expect any multiple-word searches to return 0 for sub_results, and any single word searches to return NULL, however, it seems like the opposite is happening. Like this:
phrase | count | sub_results ---------+-------+------------- foo bar | 14 | 0 // multiple-word search with no results, even in the aggregate sum of sub results map | 5 | NULL // single-word searches have no sub searches, so they will be NULL
EDIT: Sorry, StackExchange is being partially blocked by my company's firewall so I can't add any comments. @ypercube I don't want to count the sub searches (because they're not performed by the user) in the count. The count should only be how many times that phrase was searched by a user.
Basically I want all searches that got no results. If a search had sub searches with results, then I'm not worried about that because to the user, they got results.
Also, I don't want distinct id's because that would not be an accurate count. It should group the s1.phrases together and ignore the ids.
I made an SQLFiddle: http://sqlfiddle.com/#!3/a2913/2