SQL query for searches - stumbling blindly in the dark!

[ozinm]

I'm trying to build a query that seaches keywords in a table and displays matching results in order of best match.

To do this, I thought I'd write a bit of code that takes the search words, writes SELECT statements looking for each word in turn, UNION ALL the results together and do some group totalling to find out wich record got the most hits.

Here's what I've done so far:

My code generates the following SQL query when you supply it with (for example) "email delete save":

SELECT Cases.CaseID
FROM Cases
WHERE (((Cases.keywords) Like "*email*"))
UNION ALL
SELECT Cases.CaseID
FROM Cases
WHERE (((Cases.keywords) Like "*delete*"))
UNION ALL SELECT Cases.CaseID
FROM Cases
WHERE (((Cases.keywords) Like "*save*"));​

Could someone point out how I'd finish this query to produce a set of results that list the CaseID and the ammount of times it was found in the search?

Any and all help greatly appriciated as allways.

 

[FoFa]

SELECT Cases.CaseID, "email" as Target
FROM Cases
WHERE (((Cases.keywords) Like "*email*")) 
UNION ALL
SELECT Cases.CaseID, "delete" as Target
FROM Cases
WHERE (((Cases.keywords) Like "*delete*")) 
UNION ALL SELECT Cases.CaseID, "save" as Target
FROM Cases
WHERE (((Cases.keywords) Like "*save*"));

SELECT Count(CaseID) as NumMatches, Target
FROM MyUNionQuery
Group By Target

This would then give the counts for each Target, you just need to include the target on each query in your union.

 

[ozinm]

Ah I may not have explained myself properly in my original message.
what I'm after is a list in decending order of the records that contain the most of the keywords searched for.

Also because of the way I'm writing the code I'd like to do it with a single SQL query rather than one query looking at another (does that make sense?).

here's the kind of thing I'm looking for:


Table: Cases
CaseID|Keywords
1|"Internet IP Address Gateway"
2|"Save File Delete Email"
3|"Delete Word"
4|"Save Excel"
5|"Save Email"

Query Output wanted from searching for "save delete email":
CaseID|Hits
2|3
5|2
3|1
4|1


NB: I don't need the output in CSV format, I've just shown it that way here so you can get an idea of the data.

Marc

 

[FoFa]

Select CaseID, count(CaseID) as Hits
From MyUnionQuery

You would have to base this query on your union query, but the problem I am assuming is your Union query is going to be dynamic. Now you have to resort to something Access is not that good at.

Select T.CaseID, count(T.CaseID) as Hits
FROM (SELECT Cases.CaseID
FROM Cases
WHERE (((Cases.keywords) Like "*email*")) 
UNION ALL
SELECT Cases.CaseID
FROM Cases
WHERE (((Cases.keywords) Like "*delete*")) 
UNION ALL SELECT Cases.CaseID
FROM Cases
WHERE (((Cases.keywords) Like "*save*"))) AS T
Group By T.CaseID;

Basically you are setting up a logical table (your Union query) and referencing that. Might depend on the version of access you have if you can do this or not. Works in my O2K version.

 

[ozinm]

Perfect!
Worked like a dream!

Thanks again

Marc