SQL for making suggestions when no results are found
Would love some help with a search hangar I am trying to do for a karaoke music site using ASP.NET and SQL SERVER ...
I have a table named Disks that has the following fields:
ID, DiscCode, DiscTitle, DiscType, Subject, Manufacturer
There is also a table named Tracks with the following fields
ID, DiscID, Artist, Title
OK, so imagine the user is searching for the next ...
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';
This will work fine, but if no results are found, I really want to tell the user how many results they will get if any of the filters were removed with a counter like ebay ...
Sorry, no results were found, but we found similar results.
Various artists (13)
Various manufacturers (4)
Various themes (2)
The client has 2 conditions for us. A) he wants to use the "startswith" match for every element, hence the wildcard at the end of every LIKE, and B) he does not want to use the MSSQL Full TEXT search.
I don't want to do a lot of searching as this will really slow things down and instinctively feels like there must be some way to do this using grouping and counting.
Any help would be really appreciated.
John
source to share
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 a friendly index as it runs three separate queries, each using its own plan.
source to share