montypython1
Technical User
Greetings,
SQL is humbling me. I am trying to grab some information from a child table and place it into a parent table. This sounds like a perfect application for LEFT OUTER JOIN (or so I thought).
My sample database contains 2 tables. My parent table (called "csr_Jrnl_All_Exp" (for Expenses)) contains 127 records. My child table (called "csr_Jrnl_All_Pybl" (for Payables)) contains 117 records. My resulting query displays 2105 records instead of the 127 records in the Expense table.
The only thing that is unusual is that the field "RefNum" is NOT a "key" field, since one RefNum may contain multiple accounting entries. Is this why I am receiving more records than my parent table contains?
Even if the parent table contains the same reference number 10 times, I think that I should be able to pull my desired field from the child table (since there are matching "RefNum"s in both tables. And shouldn't the LEFT JOIN restrict the output to just the number of records in the parent table?
My code is below ... Any ideas are appreciated.
Thanks,
Dave Higgins
**************************************************
SELECT csr_Jrnl_All_Exp.refnum , csr_Jrnl_All_Exp.amount ;
FROM csr_Jrnl_All_Exp ;
LEFT OUTER JOIN csr_Jrnl_All_Pybl ;
ON csr_Jrnl_All_Exp.RefNum == csr_Jrnl_All_Pybl.RefNum ;
INTO CURSOR csr_Jrnl_Link_Exp_with_Pybl NOFILTER READWRITE
**************************************************
SQL is humbling me. I am trying to grab some information from a child table and place it into a parent table. This sounds like a perfect application for LEFT OUTER JOIN (or so I thought).
My sample database contains 2 tables. My parent table (called "csr_Jrnl_All_Exp" (for Expenses)) contains 127 records. My child table (called "csr_Jrnl_All_Pybl" (for Payables)) contains 117 records. My resulting query displays 2105 records instead of the 127 records in the Expense table.
The only thing that is unusual is that the field "RefNum" is NOT a "key" field, since one RefNum may contain multiple accounting entries. Is this why I am receiving more records than my parent table contains?
Even if the parent table contains the same reference number 10 times, I think that I should be able to pull my desired field from the child table (since there are matching "RefNum"s in both tables. And shouldn't the LEFT JOIN restrict the output to just the number of records in the parent table?
My code is below ... Any ideas are appreciated.
Thanks,
Dave Higgins
**************************************************
SELECT csr_Jrnl_All_Exp.refnum , csr_Jrnl_All_Exp.amount ;
FROM csr_Jrnl_All_Exp ;
LEFT OUTER JOIN csr_Jrnl_All_Pybl ;
ON csr_Jrnl_All_Exp.RefNum == csr_Jrnl_All_Pybl.RefNum ;
INTO CURSOR csr_Jrnl_Link_Exp_with_Pybl NOFILTER READWRITE
**************************************************