UPDATE: Ok, I played around with the final line to see if it recognized any other fields in either vh. or qry2 and now it is only erroring out on the qry2.vehi_id portion. If I change that to qry2.inci_id then it returns no records (well it shouldn't since the two fields will never match) but it also doesn't produce an error. It appears that for some reason qry2 does not contain the vehi_id field...




Don't know how I overlooked that one myself. I think I have been looking at this too long. I am using the exact same code as you posted below, but now the only portion which seems to create a problem is the final line:

ON vh.veh_id = qry2.vehi_id

The error returned is: " Invalid column name 'vehi_id' " for both sides of the statement which is impossible since I am pulling SELECT tw.veh_id FROM table1 tw in the query...

If I change it to: SELECT tw.* from table1 tw I get the same error message...


>>In the final result set, I need all fields from table3, I also need all fields from table 2 eventually, but to pick out the appropriate matching records from table3 I only need the vehi_id field in the result set that creates qry2.
>>
>>However, with your code changes, I still receive the error: "The column 'inci_id' was specified multiple times for 'qry2' which baffles me because the qry2 result qry is identical as when it id run alone. The only thing I am doing is putting it inside of the () inner join statement in the final query.
>
>Sorry, my mistake, you must use ALIAS in field list and WHEREs not actual table names
>

>SELECT vh.* from table3 as vh
> inner join  (SELECT tw.veh_id FROM table1 tw
>                     INNER JOIN (SELECT table2.inci_id FROM 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
>

>
>
>If you want all fields form table2 try this:
>

>SELECT * from table3 as vh
> inner join  (SELECT tw.veh_id, qry1.* FROM table1 tw
>                     INNER JOIN (SELECT table2.* FROM 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
>