This seems like such a simple problem <g>.

I have a table of unique IDs with a second column for frequency (every frequency is > 0). I have another table with all possible unique IDs. I want to merge the two to produce a list of all possible unique ideas with their frequency with zero for those that are not in the first table.

So if Table1 contains IDs of 1, 2, 4, and 6 and Table2 contains IDs of 1 through 7, then I want it to produce a list like this:

ID FREQ
1 12
2 117
3 0 (or .NULL.)
4 94
5 0 (or .NULL.)
6 48
7 0 (or .NULL.)

I thought that the command was :

SELECT ID, FREQ from Table1;
RIGHT JOIN Table2 on Table1.ID=Table2.ID

But that produces .NULL.s in the first column. What am I doing wrong?

If Table2 has 300k IDs in it, would it be faster to join Table2 to Table1 as opposed to joining Table1 to Table2? I can't figure out the SQL JOIN command for that approach either.

Thanks for any help.

(PS: I know how to do this with SET RELATION)

-Bob