2

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.

For instance:

  1. A user searches for 'foo bar'
  2. 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
    
  3. 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

  • What output do you want, expect to get? – ypercubeᵀᴹ Jun 5 '15 at 15:46
  • 1
    Don't use = when checking for nulls: OR SUM(s2.results) IS NULL – ypercubeᵀᴹ Jun 5 '15 at 15:58
  • You probably also need GROUP BY s1.phrase (without the s2) – ypercubeᵀᴹ Jun 5 '15 at 15:58
  • 1
    Can you post the real query and tables involved ? Question title says you are joining the same table, query in the question has 2 tables, tbl_search , and Search. Also, as ypercube pointed out OR SUM(s2.results) = NULL is always false, so your record "foo bar | 14 | NULL" can't be part of resultset. – a1ex07 Jun 5 '15 at 16:23
  • Make an sqlfiddle with a few sample rows of the table and the expected output. I make a bet that you do want a count of distinct id ;) – ypercubeᵀᴹ Jun 5 '15 at 17:11
1

I think you may want something like the following. Note that there was an error in the SQL Fiddle, so the first query corrects the data so that the desired results are returned!

-- Correct an error in the data
-- The sub-phrases "hub" and "cap" were mapped to the original phrase "map" rather than "hub cap"
UPDATE tbl_search
SET original = 13 /* "hub cap" */
WHERE id IN (14, 15) /* "hub" and "cap" */

-- All phrases that had no results and also had no results for any of the sub-phrases
SELECT s1.phrase, COUNT(*) AS searchAttempts
FROM tbl_search s1
LEFT OUTER JOIN (
    SELECT DISTINCT original 
    FROM tbl_search
    WHERE results > 0
        AND original IS NOT NULL
) subPhrasesWithMatch
    ON subPhrasesWithMatch.original = s1.id
WHERE s1.original IS NULL /* Only original searches */
    AND s1.results = 0 /* Only searches with no results */
    AND subPhrasesWithMatch.original IS NULL /* We didn't match the join to sub-phrases that returned results */
GROUP BY s1.phrase
ORDER BY searchAttempts DESC
--phrase    searchAttempts
--foo bar   3
--map       2
--foo       1
1

How about this:

SELECT 
    No_Result_Search.search_phrase,
    Count(No_Result_Search.id) AS Search_Attempts
FROM 
(
    SELECT
        main_search.id, 
        MAX(main_search.phrase) as search_phrase 
    FROM 
        tbl_search main_search
    LEFT JOIN
        tbl_search sub_search
            ON
                main_search.id = sub_search.original
    WHERE
        main_search.results = 0
  AND main_search.original IS NULL
    GROUP BY 
        main_search.ID
    HAVING
        IsNull(Sum(sub_search.results),0) = 0
) No_Result_Search
GROUP BY
    No_Result_Search.search_phrase

You could do something like this in one query, but phrase searches that returned no results at one point and results at another point will be filtered out:

SELECT
    main_search.phrase,
    Count(DISTINCT main_search.id) AS Search_Attempts
FROM 
    tbl_search main_search
LEFT JOIN
    tbl_search sub_search
        ON
            main_search.id = sub_search.original
WHERE
    main_search.results = 0
AND main_search.original IS NULL
GROUP BY
    main_search.phrase
HAVING 
    IsNull(SUM(sub_search.results),0) = 0
  • This is very close. It looks like it successfully combines multiple-word attempts into one record, but it doesn't include single-word attempts. Here's check out the SQLFiddle (I added your above SQL) sqlfiddle.com/#!3/6d0c13/1 – Travis Heeter Jun 5 '15 at 19:04
  • To include single word searches, change "Sum(sub_search.results) = 0" to "IsNull(Sum(sub_search.results),0) = 0". I'll edit my answer to include that. Sorry, wrote it without testing. – Shane Estelle Jun 5 '15 at 19:09
  • Also added " AND main_search.original IS NULL" clause to inner query. – Shane Estelle Jun 5 '15 at 19:17
  • It sounds like my second query solution is the solution you are looking for, since it won't return search phrases that returned no results only sometimes. It returns hub cap because it is actually search ID 13, while the sub searches have original id set to 11 in the test data. Map was not returned, because the sub searches for hub cap are tied to ID 11, which is the map search, so it thinks map had sub searches that returned results. – Shane Estelle Jun 5 '15 at 19:24
  • Sorry I missed this last comment before posting my answer. I agree with Shane; this approach works once the data in the SQL Fiddle is corrected. My answer is just one alternative way to re-phrase this logic, which I didn't realize at first due to the bad mapping in the test data! – Geoff Patterson Jun 5 '15 at 21:42
1

I think you want this:

SELECT 
      s1.phrase, 
      COUNT(DISTINCT s1.id) 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
   AND s1.original IS NULL
 GROUP BY s1.phrase
 HAVING SUM(s2.results) = 0
     OR SUM(s2.results) IS NULL
 ORDER BY count DESC ;
  • The COUNT(DISTINCT s1.id) is because you may have more than one searches with same phrase (but different id).

Tested at SQLfiddle.


If you don't want to see the sub_results column, you can use the simpler:

SELECT 
     s1.phrase, 
     COUNT(*) AS count
FROM tbl_search s1
WHERE s1.results = 0
  AND s1.original IS NULL
  AND NOT EXISTS
      ( SELECT *
        FROM tbl_search s2 
        WHERE s1.id = s2.original
          AND s2.results > 0
      )
GROUP BY s1.phrase
ORDER BY count DESC ;
  • Finally able to add comments again. Sorry about that. Here's the fiddle, with desired outcome. sqlfiddle.com/#!3/6d0c13/1 – Travis Heeter Jun 5 '15 at 19:04
  • I've edited the query and tested with your fiddle (after correcting it because the id were not matching the data.) – ypercubeᵀᴹ Jun 5 '15 at 22:13
  • The second one is giving me more accurate results. The first one seems to only find single-word searches. Also, all the id's will be distinct, as they are the primary key, so I'm not sure why we'd put DISTINCT id's. original will have duplicates however. – Travis Heeter Jun 8 '15 at 11:48
  • About distinct: you have GROUP BY s1.phrase in your query. I assumed that you may have multiple searches (so different ids) with the same string. – ypercubeᵀᴹ Jun 8 '15 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.