>>>I am trying to figure out how to use a subquery in the following example:
>>>
>>>Table1
>>>PK_FLD, TASK_NO
>>>
>>>Table2
>>>TASK_NO, FLDA, FLDB
>>>
>>>I want the resulting query to have as many records as there are in Table1 adding columns FLDA and FLDB from
>>>Table2 according to the matching TASK_NO.
>>>
>>>My attempt at this query is as following
>>>
>>>Select Table1.PK_FLD, Table1.TASK_NO, T2.FLDA, T2.FLDB from Table1 where (select * from Table2 where
>>>TASK_NO = Table1.TASK_NO) T2
>>>
>>>
>>>But I get error of incorrect use of subquery. What am I missing? TIA.
>>
>>use JOIN instead of subquery.
>>
>>Select Table1.PK_FLD,;
>> Table1.TASK_NO,;
>> T2.FLDA,;
>> T2.FLDB;
>>from Table1 ;
>>INNER JOIN Table2 T2 ON T2.TASK_NO = Table1.TASK_NO;
>>** You could use LEFT JOIN if you want ALL records from Table1 and only matching records from Table2
>>
>
>I am sorry that I probably mislead you (and Yuri) since I didn't indicate that I want records from Table2 (that do non have matching to Table1 on TASK_NO). That is, I want all records from Table1 and the records from Table2 that do not have matches. I think INNER or LEFT JOINT won't give me that. At least I tried.
>
>Thank you and Yuri.
In this case, use NOT EXISTS subquery, e.g.
select T.* from Table1 where not exists (select 1 from Table2 where Table2.Task_no = Table1.Task_No)
If it's not broken, fix it until it is.
My Blog