SQL for making suggestions when no results found.

Question

Hi.

Would really appreciate some help with a search angine I'm trying to make for a karaoke music site using ASP.NET and SQL SERVER...

I have a table called Discs which has the following fields:

ID, DiscCode, DiscTitle, DiscType, Theme, Manufacturer

There is also a table called Tracks with the following fields

ID, DiscID, Artist, Title

OK, so imagine a user performs a search for the following...

Artist: Michael Jackson
Title: Thriller
Theme: Pop
Manufacturer: Sunfly
DiscType: cdg

I would use a SQL statement like this....

SELECT D.ID, T.Artist, T.Title, D.Manufacturer, D.DiscTitle 
FROM Discs D
INNER JOIN Tracks T
ON T.DiscID = D.ID 
WHERE T.Artist LIKE 'Michael JAckson%' 
AND T.Title LIKE 'Thriller%' 
AND D.Theme = 'Pop' 
AND D.Manufacturer = 'Sunfly' 
AND DiscType = 'DVD';

That will work fine, but if no results are found I really want to be able to tell the user how many results they would get if any of the filters were removed with a count, like ebay does...

Sorry, No results were found but we did find some similar results.

Different Artists (13)

Different Manufacturers (4)

Different Themes (2)

The client has 2 stipulations for us. A) he wants to use a "startswith" match on each item, hence the wildcard at the end of each LIKE , and B) he doesn't want to use MSSQL Full TEXT searching.

I don't want to have to do lots of searches as this will really slow things down and instinctively it feels like there should be some way of doing this by using crouping and counts.

Any help would really be appreciated.

Jon

Answer 1

You basically have 2 choices: (1) run the partial queries each time, or (2) maintain an aggregate count table that is periodically updated.

On the assumption that most searches on average will return hits, I would go for option (1) and ensure that you have good indexes in place.

Answer 2

SELECT  SUM(CASE WHEN cnt = 3 THEN 1 ELSE 0 END) AS exact_matches,
        SUM(CASE WHEN artist LIKE 'Michael Jackson%' THEN 0 ELSE 1 END) AS diff_artist,
        SUM(CASE WHEN theme = 'Pop' THEN 0 ELSE 1 END) AS diff_theme,
        SUM(CASE WHEN manufacturer = 'Sunfly' THEN 0 ELSE 1 END) AS diff_manufacturer
FROM    (
        SELECT  t.id, COUNT(*)  AS cnt
        FROM    (
                SELECT  t.id
                FROM    tracks t
                JOIN    discs d
                ON      t.discID = d.id
                WHERE   t.artist LIKE 'Michael Jackson%'
                UNION ALL
                SELECT  t.id
                FROM    tracks t
                JOIN    discs d
                ON      t.discID = d.id
                WHERE   d.theme  = 'Pop'
                UNION ALL
                SELECT  t.id
                FROM    tracks t
                JOIN    discs d
                ON      t.discID = d.id
                WHERE   d.manufacturer = 'Sunfly'
                ) q
        GROUP BY
                t.id
        HAVING  COUNT(*) >= 2
        ) q2
JOIN    table t
ON      t.id = q2.id
JOIN    discs d
ON      d.id = t.discID

This is index friendly, since it runs three separate queries, each on them using its very own plan.