>>>>>>>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.
>>>>
>>>>
>>>>Don't get it.
>>>>Some example data and desired result?
>>>
>>>Here a sample code I am playing with:
>>>
>>>CREATE CURSOR Table1 (pk_fld i, task_no i)
>>>INSERT INTO table1 VALUES (1,1)
>>>INSERT INTO table1 VALUES (1,2)
>>>CREATE CURSOR Table2 (task_no i, flda c(1), fldb c(1))
>>>INSERT INTO table2 VALUES (1,"A","B")
>>>INSERT INTO table2 VALUES (2,"C","D")
>>>INSERT INTO table2 VALUES (3,"C","F")
>>>INSERT INTO table2 VALUES (4,"K","I")
>>>
>>>
>>>Keep in mind that the above case has more records in Table2 than in Table 1 and for this FULL JOIN seem to work. But I need to test it in the case when Table1 has more records than Table2.
>>
>>Why do you have duplicates in the PK field in the table1? Are you sure you're using the correct code (or PK field is not a primary key and only named so to confuse everyone)?
>
>I think the PK here has nothing to do whit the query :-)
>It can have whatever value you want :-) and Dmitry just copy & paste the records :-)
You are correct. This PK is not PK for the table but PK from another table.
"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