>I have these three tables -
>

>Table: Products
>ProdId	Prod_Series 			Prod_No
>------------------------------------------------
>6090	ML350T02 P1266 2059 FR		291378-051
>6091	ML350T02 P1266 512 M1 BRZL	225861-201
>6092	ML350T02 P1266 EU2002 EURO	277811-421
>6093	ML350T02 P1266 SP3636 EURO	267546-421
>
>Table: ProductParts
>RowId	ProdId	PartId
>------------------------------------------------
>246432	6090	5526
>246433	6091	5067
>246434	6091	4638
>246435	6091	4639
>246436	6091	4682
>
>Table: Parts
>PartId	Part_Num	Keyword		Category			Desc
>------------------------------------------------------------------------------------------------
>5762	228523-001	Cover			Mechanical PartsAccess panel (top cover) - Includes built-in hood latch
>5763	228524-001	Processor		Logic Board			Intel Pentium III processor - 1.13GHz (Tualatin, 133MHz front...
>5764	228525-001	Airflow Guide	Mechanical Parts	Processor airflow guide (air baffle)
>5765	228526-001	Manual/Software	Mechanical Parts	Localization kit (country kit) - Use with 2.40GHz and 2...
>5766	232793-001	Rack Mount Kit	Mechanical Parts	Rack mount kit - Includes left slide assembly for stand...
>5767	232794-001	Rack Mount Kit	Mechanical Parts	Rack mount kit - Rails for 2-post Telco type rack
>
>
>

>
>
>The user can enter anything for a query. If the query string is found in Product.Prod_Series or Product.Prod_No, the return the
>Product row and all child rows for it. If the query string is found in the Part.Part_Num, Part.Keyword, Part.Category, or Part.Desc,
>then return the Product Row the part belongs to, as well as the part row.
>
>Think TreeView. If the text I search for is a parent, return the parent and all children. If the search text is a child, return the only
>the child an it's parent.
>
>Is this possible in one query? Anyone have any thoughts on how to do this?

DECLARE @p0 VarChar(10) = '225861-201'
--DECLARE @p0 VarChar(10) = '228526-001'

set nocount on;
declare @products int
select @products = COUNT(*)
  FROM Products t1
  WHERE (t1.Prod_Series = @p0) OR (t1.Prod_No = @p0); 

if @products = 0
  SELECT * 
    FROM Parts
    WHERE (Part_Num = @p0) OR (Keyword = @p0) OR (Category = @p0) OR (Descr = @p0);
else   
  SELECT t1.*, t2.*
    FROM Products t1
       LEFT OUTER JOIN ProductParts t2 ON t1.ProdId = t2.ProdId
       LEFT OUTER JOIN Parts t3 ON t2.PartId = t3.PartId
    WHERE (t1.Prod_Series = @p0) OR (t1.Prod_No = @p0);

Cetin