Searching two SQL 7.0 tables at once

By Bill Graziano on 21 August 2000 | Tags: SELECT

Gavin writes "I have three tables, Product, FactSheet and FactSheetItem. A Product can have one fact sheet, that in turn has many Items, however a Product does not have to have a Fact Sheet. I need to perform a search on the Product name, and the related FactSheetItem descriptions for . . .

"1. All requested words (in no order)
2. Any requested words (in no order)
3. Exact Phrase

A record will be returned when any of the fields in the record meet the above criteria

Problems encountered are :

1. It is returning the same product more than once
2. It is only searching for Products that have FactSheets

I need to be able to do this in SQL before I send it to the Web server, as there can potentially be alot of records"

There are a couple of things you need to accomplish here The first is two get your main query with the join correct. You might write your query something like this:

SELECT DISTINCT P.ProductID
FROM Product P
Left Join FactSheet F ON P.ProductID = F.ProductID
Left Join FactSheetItem FSI ON F.FactsheetID = FSI.FactSheetID

This will return all your Product records even if they don't have a FactSheet or FactSheetItems record. The LEFT JOIN clause instructs SQL Server to return all the rows in the left side table regardless of whether any recprds are in the other table. Any time this query returns a Product without a Factsheet record, all the fields from Factsheet (or FactSheetItems) will be null.

The second key element is the DISINCT clause. This instructs SQL Server to only return distinct values (each value once).

The WHERE clause is trickier. Based on what you want to accomplish you will have to dynamically construct your where clause. You can easily search for a field that contains a value with a static SQL statement. Searching for multiple requested words or an exact phrase requires something a little more complex. That logic I'll leave for you. You might check out Full-Text Indexing though. You can do some neat things with it.