Unlike Nadya's solution, your description of the problem makes me immediately think of a UNION. That is, I think of JOIN more in the sense of horizontally combining tables; and UNION in the sense of combining tables vertically.
select PkField from Table1;
UNION select PkFIeld from Table2;
into cursor Tmp
Note that, if you don't specify a DISTINCT clause, duplicates will be eliminated automatically.
It seems to me, however, that Nadya's approach is just as sound, and that you could do it either way. My sample represents my personal preference in this case.
>Hi everybody,
>
>I am struggling to create one SQL select that will create a query as following (simplified):
>
>
>1. table mytable1 has the following records:
>
>PK_VAL
>1
>2
>3
>
>2. table mytable2 has the following records:
>PK_VAL
>1
>2
>5
>6
>
>
>I want to create a query of all records in mytable1 plus records in mytable2 that have PKs missing in mytable1. In the example above, the resulting query would have the following records:
>
>PK_VAL
>1 (from mytable1)
>2 (from mytable1)
>3 (from mytable1)
>5 (from mytable2)
>6 (from mytable2).
>
>
>Thank you in advance for any help.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)