ONIONS

[DomTrix]

UNION selects unique items from two or more SELECT queries, and UNION ALL selects all items from two or more select queries.

Question: Is there a way to use UNION to select records ONLY when they appear in ALL SELECT queries?

thanks

dt

 

[vongrunt]

In all SELECT queries... nope. Unless you use SELECT... GROUP BY... HAVING COUNT(*) = on UNION ALL.

How about good ol' inner join?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DomTrix]

Yes, I am trying to work around a problem tho that I cant figure with inner join.

The problem is basically this:

@ I am searching for products contained within a single table based on a number of search terms (title, author, ISBN, etc.) that will be searched on several, related, tables.

@ The search terms can be blank, meaning that they will be ignored. i.e. if 'ISBN' is left blank, all ISBNs will be returned.

@ I want to use FREETEXTTABLE to achieve this but it returns an error if I try passing it a string like ' "*" ' so I need to check for that, i.e. if @searchISBN = "" dont inner join this table...


I am finding it hard to explain this so its probably not very clear. Its doing my nut in any case!

 

[DomTrix]

Ok, I have the code working with 'valid' data input into each of the search criteria, i.e. no 'noise words'.
The code looks something like this (slimmed down):

CREATE PROCEDURE dbo.selItemByAdvancedSearch
@searchTitle		varchar(100),
@searchAuthor		varchar(100)

AS

SELECT * 
FROM product

INNER JOIN FREETEXTTABLE(product, pro_Title, @searchTitle) AS key_table_title
ON product.pro_ID = key_table_title.[KEY]

INNER JOIN author
ON author.aut_ID = product.pro_author

INNER JOIN FREETEXTTABLE(author, aut_Name,    @searchAuthor) AS key_table_author
ON author.aut_Name = key_table_author.[KEY]

It works perfectly, it will only return records where the record is in each FREETEXTTABLE recordset.

The problem is when, as we would like it, the user does not enter anything for say @searchAuthor. The behaviour we want is that it will return ANY author and not return the lovely FREETEXT error:

Syntax error in search condition, or empty or null search condition ''.

Is there a valid wildcard for FREETEXT? I have tried '*' and ' "*" ' but get a noise word type error.

If no wildcard, how can I get the query to not do the INNER JOIN on authors when @searchAuthor = ''??

Thanks in hope

DT