Hi Larry,

Really appreciate your reply to my post. Unfortunately, the rows where a value doesn't exist in Tmp3 are still not being displayed. I'm completely baffled!

Anyway, thanks for your help.

Dan


Dan,
You don't need a FULL JOIN, you only need a LEFT JOIN. Try the following:
SELECT nvl(t.gpcode,'00000') gpcode, ag.agegp as aagegp, ag.sex as asex, nvl(t.cnt,0) cnt ;
from agesex ag LEFT OUTER JOIN tmp3 t ON ag.agegp=t.agegp AND ag.sex=t.sex ;
INTO TABLE tmp5 DATABASE raw ;
ORDER BY gpcode, a.agegp, a.sex
Because you are using an outer jopin, you should use the NVL() function to specify a default value if a corresponding row does not exist in Tmp3.

Also, I changed the alias from a to ag. VFP has problems sometimes when single letter aliases from A-J are used. They are internally designated for the first ten work areas.

HTH.

>Hi all,
>
>I can't get an SQL query to work properly, & am hoping that the SQL experts among you may be able to help.
>
>I have 2 tables:
>-'Agesex'- a 'lookup' table for age/sex groups, eg:
>Agegp Sex
>00-04 0
>00-04 1
>05-14 0
>05-14 1
>
>- 'Tmp3' - a table of the number of patients (cnt) GP's (gpcode) have of each age/sex group, eg:
>
>Gpcode Agegp Sex Cnt
>00001 00-04 0 204
>00001 00-04 1 180
>00001 05-14 0 79
>00001 05-14 1 96
>
>What I'd like is to outer join the tables so that the full 'Agesex' table is reproduced for every Gpcode, even when a GP has no patients of a particular age/sex group. I've tried the following without luck, so any help appreciated.
>
>SELECT t.*, a.agegp as aagegp, a.sex as asex from agesex a FULL OUTER JOIN tmp3 t ON a.agegp=t.agegp AND a.sex=t.sex INTO TABLE tmp5 DATABASE raw ORDER BY gpcode, a.agegp, a.sex
>
>TIA.