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

Cannot Show requested dialogue - Timeout Expired. The Timeout priod elapsed prior to completion of the operation or the server is not responding (84m)

Basic SQL query? (13h)

T-sql - we created Message from app1 and trying to disable from app2 (1d)

SQL select Top 10 records for unique combination of two columns (1d)

SSRS Report Sorting with Grouping Issue (2d)

ORA-01476: divisor is equal to zero (2d)

Create new columns based on min and max values of a record with multiple rows (2d)

Memory Required for Reporting Services 2022 (2d)

- Advertisement -