Left Outer Join: Why am I receiving more records than my parent table?

[montypython1]

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
**************************************************

 

[Mike Lewis]

Hi Dave,

It's perfectly normal for a join (LEFT OUTER or otherwise) to return more records than were in the original parent table. The question you should be asking yourself is whether the results contain the correct records.

Basically, what your query is doing is to create a result set where there is one record for each payable that has a matching expense, plus one further record for each expense that doesn't have a payable. (If you had omitted the LEFT OUTER, you would have got one record for each payable that has a matching expense and no more).

I'd also question your point that you want to "grab some information from a child table and place it into a parent table." Your query does not place anything in the parent table; it puts the results into a cursor, which is presumably what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

Dave - you may find this paper from my website helpful for understanding inner and outer joins:

http://www.tomorrowssolutionsllc.com/Materials/building_queries_in_visual_foxpr.htm

Tamar

 

[montypython1]

Hi Mike and Tamar,

Thank you both for for your quick responses.

Mike, you are correct ... I DO want to place the result in a cursor (rather than the parent table). My goal is to create a cursor with exactly the same number of records as the Expense (parent) table, but also display a specific field from the Payable (child) table (assuming there is a matching RefNum, of course).

I tried using SELECT DISTINCT, but this filtered out several records from the parent table that were identical (I don't have control of the structure of this table, so I have to work within the structure that is available).

Unfortunately, the result is that I am generating too many records. I can get exactly what I want using Excel's VLOOKUP function. It returns NULL values if the lookup function fails (which is okay), but it never adds to the number of records from the parent table.

Thank you both for your input and any further suggestions.

Dave

 

[Mike Lewis]

Dave,

... but also display a specific field from the Payable (child) table

What criterion are you using to identify the child record? Is it simply the first one it finds, or does it have a specific value in one of its fields, or what?

Once you've identified that criterion, you can built it into a WHERE clause.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[falconsight]

What do you need the JOIN for?
You are selecting records just from the parent table.
Also, ypu'll do better using a WHERE condition that filters on the exact records you want.

 

[montypython1]

Hi FalconSight,

Is there a better way to do this?

I thought that I needed a LEFT OUTER JOIN statement in order to pull some of the data from the child table into the parent table. My result must display all 127 records from the parent table (I don't want to filter any records from the parent table, although there will be some null values in the fields from the child table where the RefNum lookup fails to find data in the 117 records of the child table).

I am currently linking the 2 tables together, provided there is a match on the "RefNum" field. Is there a better way?

Thanks,
Dave

 

[StewartUK]

Dave,

So you want all the records from csr_Jrnl_All_Exp plus another field which is null if there is no match on table csr_Jrnl_All_Pybl but which shows some data where there is a match.

You say:

My goal is to create a cursor with exactly the same number of records as the Expense (parent) table, but also display a specific field from the Payable

You haven't got the table csr_Jrnl_All_Pybl in your field selection list.


You say:

since one RefNum may contain multiple accounting entries. Is this why I am receiving more records than my parent table contains?

Yes, that's right.


So what results do you want to see where there is more than one match?

Stewart

 

[montypython1]

Hi Stewart,

Thanks for your input.

You are correct about the missing field in the field selection list ... I accidentally deleted that field in my SQL (during my testing, I was trying to make the code as simple as possible, and got carried away). It should have also contained the field "csr_Jrnl_All_Pybl.Vdr_Name".

Regarding the multiple entries in the "csr_Jrnl_All_Pybl" file: the "Vdr_Name" field will contain the same name for each entry attached to that specific RefNum. For example, if the "csr_Jrnl_All_Pybl" has 4 entries tied to the RefNum "A123456789", each of the 4 entries will have the same vendor name in the field "Vdr_Name" (ex: "ABC Office Supply Company"). We need to pull just that vendor name one time and attach it to the "csr_Jrnl_All_EXP" table.

A perfect example of what I'm trying to do is Excel's VLOOKUP function: VLOOKUP simply finds the first match of a defined field between 2 different files, then pulls over the data that was requested.

I hope this makes sense.

Thanks,
Dave

 

[Olaf Doschke]

Hi monty,

that you have repeated values of Vdr_Name in your Jrnl_All_Pybl table shows it'd better be in the Jrnl_All_Exp table. (I assume you use the crs_ prefix for cursors, the recommended prefix is cur).

So you move in the right direction of normalisation.

Still as you have more than one child record you get these repeated. You can simply SELECT DISTINCT ...

Bye, Olaf.

 

[StewartUK]

You said that DISTINCT incorrectly removed some records you wanted to have in the results, so I think GROUP BY will get what you want:

SELECT csr_Jrnl_All_Exp.refnum, csr_Jrnl_All_Exp.amount, csr_Jrnl_All_Pybl.Vdr_Name ;
  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 GROUP BY 1,2,3

Stewart

 

[montypython1]

Hi Stewart and Olaf,

Thank you both for your suggestions ... your ideas and experience are always helpful. I will try out your suggestions when I return to my office tomorrow.

Thanks,
Dave

 

[Olaf Doschke]

Thanks for pointing out that SELECT DISTINCT was already done and did remove some parent records, I overlooked that.

Yes, then go with GROUP BY, but you need it on a subselect only (or use DISTINCT there) as grouping parent record fields again will reduce double ones.

SELECT csr_Jrnl_All_Exp.*, temp.Vdr_Name FROM csr_Jrnl_All_Exp LEFT JOIN (SELECT DISTINCT csr_Jrnl_All_Pybl.Refnum, csr_Jrnl_All_Pybl Vdr_Name FROM csr_Jrnl_All_Pybl) temp ON temp.Refnum = csr_Jrnl_All_Exp.Refnum

In case there are several Vdr_Name for the same Refnum it will still introduce some more records. Although you say this isn't the case you can make sure you only get one Vdr_Name for a Refnum by GROUP BY:

SELECT csr_Jrnl_All_Exp.*, temp.Vdr_Name FROM csr_Jrnl_All_Exp LEFT JOIN (SELECT csr_Jrnl_All_Pybl.Refnum, MAX(csr_Jrnl_All_Pybl Vdr_Name) FROM csr_Jrnl_All_Pybl GROUP BY 1) temp ON temp.Refnum = csr_Jrnl_All_Exp.Refnum

If you don't have VFP9 make the inner subselect in a seperate select ... into cursor temp nofilter. and then LEFT JOIN temp ON ... in the second select.

Bye, Olaf.