>>>>>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.
>>>>
>>>>Assuming 'Products' is the parent that implies a part is only used in a single product. Is that the case ?
>>>>>
>>>>>Is this possible in one query? Anyone have any thoughts on how to do this?
>>>
>>>No, it is not. That's why there's a ProductParts table.
>>
>>So how do you select 'the child and it's *parent* when it can have more than one ?
>
>That is the question, now isn't it
>
>I think Cetin's response will work though it might only be limited to one level.

I don't think Cetin's code (as is) attempts to select from the Products file when the search term is Parts related.
But, assuming that you will be pulling the results into a dataset, it might make more sense to return two separate tables so that the results could be handled identically whether selected based on a product or a part ? If you don't do that then can you have two completely different table structures returned from the same query.....