>Hi All,
>
>I have a simple join question. I want to join 4 tables. 3 of which are a 1 to 1 relationship. But the 4th table is 1 to many (or none). I would like to only get 1 record from that table.
>
>Name Pay_Id Addr Phone
>John Doe xyz Somewhere 123-456-7890 && This 1 I want
>John Doe xyz Somewhere 987-654-3210 && This 1 I don’t want.
>
>Can this be done? Or should I get the info from the 3 tables, then the phone numbers, and just use the 1st I find?
>
>SELECT EMP.pay_id, EMP.birth_day, EMP.in_dt, EMP.out_dt, EMP.seg_no, EMP.hrs_rate, EMP.hrs_base, EMP.sin, PERS.pers_no, PERS.last_name, PERS.first_name, PERS.gender_cd, ALLT(ADDR_DTL.addr_line) AS addr_line, ADDR_DTL.city, ADDR_DTL.zip_cd, NVL(PERS_PHONE.phone_num, SPACE(10)) AS phone_num, .F. AS lexport ;
>FROM main_one!PERS LEFT OUTER JOIN main_one!EMP ;
>ON PERS.pers_no = EMP.pers_no ;
>LEFT OUTER JOIN main_one!ADDR_DTL ;
>ON PERS.addr_no = ADDR_DTL.addr_no ;
>LEFT OUTER JOIN main_one!PERS_PHONE ;
>ON PERS.pers_no = PERS_PHONE.pers_no ;
>WHERE ISNULL(EMP.out_dt);
>ORDER BY PERS.pers_no
>
>TIA
>Mike
>
>PS. This is being done through the VFP ODBC

Would not a
GROUP BY PERS.pers_no
before the order clause do this? Although there is no guarantee concerning which record from the multi-record table you'll get.

Mike