Searching two SQL 7.0 tables at once

By Bill Graziano on 21 August 2000 | 0 Comments | 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.

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

grouping sets (3 Replies)

Code entry - cursor does not advance to next line (1 Reply)

iterate through many columns to search for a value (3 Replies)

iif statement comparing two fields (1 Reply)

Counting Rows (1 Reply)

Turning Rows into Columns (13 Replies)

Error in pivot statement (1 Reply)

sql query help eith grouping sets (4 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -