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

SQL Server to PostgreSQL (47m)

SQL Union Create Custom Field to Different 2 Tables Output (5h)

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (13h)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (13h)

Filter query base on date field (4d)

Pull results from a stored procedure (4d)

Help With Query To Count Number of Pets & Vaccinations (5d)

Connect sql off network (5d)

- Advertisement -