Union query with subqueries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Union query with subqueries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-18 : 09:16:22

Jochem writes "Hi,

Probably there is a better solution then the one I came up with, but for the moment I am also really stuck with my solution.

I tried to make an 'intelligent' searchform (in ASP) for a user database. No radiobuttons for options like 'search for any words', '...all words', etc., no checkboxes or pulldowns in my form, just a textbox and submit button (like google has).

The idea was when the whole string (e.g. 'vincent van gogh') was found in one of the columns in the database the score returned was 100. Next, I searched with a union select on each word within the string (e.g. vincent, van, gogh). When there was a 100% hit on for example vincent the score ADDED to the existing scores was 95, etc. I will post the code I used in my ASP page on the bottom so you will have an impression on what I am doing.

This worked fine with an access database, but does not work with SQL 2000. What am i doing wrong? Is there a better way to do the kind of search i want to perform?

Thanx i advance,

Jochem

--ASP CODE with SQL query --

'100% hits for z
SQL = SQL & "Select 90 as score, * from tbl_itsm WHERE "
SQL = SQL & " ([Last name] = '" & z
SQL = SQL & "' OR [First name] = '" & z
SQL = SQL & "' OR [search code] = '" & z & "') union "

'starting with z
SQL = SQL & "Select 80 as score, * from tbl_itsm WHERE "
SQL = SQL & " ([Last name] like '" & z
SQL = SQL & "%' OR [First name] like '" & z
SQL = SQL & "%') union "

'end on z
SQL = SQL & "Select 70 as score, * from tbl_itsm WHERE "
SQL = SQL & " ([Last name] like '%" & z
SQL = SQL & "' OR [First name] like '%" & z & "')"

ETCETERA (there are more unions) THEN I DID THE FOLLOWING:

sql2 = "select sum(score),[last name],[first name],[search code] from (" & sql & ") group by [last name],[first name],[search code] Having (sum(score) > " & minimumscore & ") order by 1 desc, 2"

-- END ASP CODE --

Hope you get the picture of what I am TRYING to do... ;-)

Grz. Jochem"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-18 : 09:54:22

Jochem, i cant really see anything wrong with the syntax of the code, but i think this is a good case for you to consider "Full-Text Indexing". Look it up in the Books Online, it just might save you a lot of code

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-18 : 10:27:00

One thing you can do is create a stored procedure that parses the string by word into a table variable; search the site for CSV for info on how to do this.

Then do a cross join with that table of words and matches, group by the key field in the table you are searching, and calculate the COUNT(*) for each key field -- that is the # of matches. use the COUNT(*) divided by the total # of words to determine your match %.

Pseudo code:

CREATE PROCEDURE ReturnMatches
@SearchString varchar(1000)

declare @t table (i identity primary key, word varchar(100))

-- parse your @SearchString by word and insert it into the table:
insert into @t (word) ... some code here, insert each word ...

-- Now, return a list of primary keys of the rows that match
-- and how well they match:

SELECT KeyField, 1.0 * COUNT(*) / (SELECT COUNT(*) FROM @t) as MatchPct
FROM
TableToSearch
CROSS JOIN
@t
WHERE TableToSearch.Field1 LIKE '%' + @t.word + '%' OR
TableToSearch.Field2 LIKE '%' + @t.word + '%'
..etc...
GROUP BY KeyField

I hope that helps ... but a full text search may be the way to go.

- Jeff

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-04-18 : 17:12:16

You never told in which sense it did not work.

One thing is that a derived table in Tsql must have a correlation name


from (" & sql & ") dt

Edited by - LarsG on 04/18/2003 17:12:43

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-18 : 17:26:33

Could this be the problem? Need to see all of your query.

From BOL

quote:

if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources