UNION function - 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 function

Author

Topic

Ferox
Starting Member

18 Posts

Posted - 2005-08-24 : 09:10:47

I have a search engine where users can search against fields with full-text indexing. The user can make individual searches which are summarised & displayed. The user has the option to combine searches, so in the example below the user has initially searched for 'apple', then 'banana'.

e.g.
1. SELECT count([ID]) as myCount FROM [myTable] WHERE CONTAINS(*, 'apple') // returns 20 records
2. SELECT count([ID]) as myCount FROM [myTable] WHERE CONTAINS(*, 'banana') // returns 30 records

The wish to combine their search. So far I've been combining the search to result in a query like this:

SELECT count([ID]) as myCount FROM [myTable] WHERE CONTAINS(*, 'apple') AND CONTAINS(*, 'banana')
// returns 10 records

But the users are running more complicated searches and it was suggested that I try using a UNION:

e.g.
SELECT [ID] FROM [myTable] WHERE CONTAINS(*, 'apple')
UNION
SELECT [ID] FROM [myTable] WHERE CONTAINS(*, 'banana')

For some reason tho, I'm getting all the records back (50 in total), where as i though I should be getting 10 records back. Am I using the UNION function incorrectly?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 09:18:48

Union Operator will suppress the duplicate rows
To see all records use Union All

and whats wrong with default font size?

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-24 : 09:20:43

Using the function wrongly...nope.
however the code
"SELECT [ID] FROM [myTable] WHERE CONTAINS(*, 'apple')
UNION
SELECT [ID] FROM [myTable] WHERE CONTAINS(*, 'banana')"

in some cases is the as
"SELECT [ID] FROM [myTable] WHERE CONTAINS(*, 'apple') or CONTAINS(*, 'banana')"

UNION ALL will give a slightly different resultset....read up on it in BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources