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.

Related Articles Joining to the Next Sequential Row (2 April 2008) Writing Outer Joins in T-SQL (11 February 2008) How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007) How to Use GROUP BY in SQL Server (30 July 2007) SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007) Server Side Paging using SQL Server 2005 (4 January 2007) Using XQuery, New Large DataTypes, and More (9 May 2006) Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts Query performance Call Center data (20h) Looking for on Premises tool to read data from SQL logs and populate data warehouse (1d) Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (1d) Working with multiple WHERE statements (1d) Create a new field value that shows a difference in value from 2 fields (3d) Hierarchy wise Sales Targets (3d) Get the MaxDate in results (5d) Switch Statement within a SSRS SQL Expression (6d)