>>>>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)
Please see my reply to Borislav,again. It looks like FULL JOIN will work. I am still testing various scenarios though. Thank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham