Ok, you are not (well maybe you will :o) going to believe how stupid this is. The field in both tables is vehi_id, not vehi_id in one and veh_id in the other. Now I KNOW I am seeing things when I didn't catch that ever last night! :o) I looked and looked at it looking for typos and never saw that!



>>Got it working! :o) *GGGGGGGG* with the following two changes:
>>
>>SELECT * from table3 as vh
>> INNER JOIN (SELECT tw.* from table1 as tw...
>>
>>which is almost identical and makes no sense to me, but it returns the correct results. Yahoo! Thanks, thanks, thanks!
>>
>
>I'm glad you got it work, but that is strange to me because I tested it that way:
>
>

>DECLARE @Table3 TABLE (vehi_id Int)
>DECLARE @Table2 TABLE (inci_id varchar(14), parent_id varchar(14))
>DECLARE @Table1 TABLE (veh_id Int, inci_id varchar(14), canccode varchar(20))
>
>
>SELECT * from @table3 as vh
> inner join  (SELECT tw.veh_id, qry1.* FROM @table1 tw
>                     INNER JOIN (SELECT table2.* FROM @table2 table2
>                                        WHERE  table2.inci_id = '07000244      '
>                                            or table2.inci_id = '2007093878    '
>                                            or table2.parent_id = '07000244      '
>                                            or (LEN(table2.parent_id)>0 AND table2.parent_id = '2007093878    ')) qry1
>                     ON tw.inci_id = qry1.inci_id WHERE canccode='    ') qry2
>on vh.vehi_id = qry2.veh_id
>

>and no error happens.
>(of course no result also, but the there should be errors if something is wrong.