>>>>>>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.....
I agree. Good idea.
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people