?
Solved

Left Outer Join

Posted on 2003-02-24
19
Medium Priority
?
645 Views
Last Modified: 2012-08-13
I have a primary table linked to a secondary table using a Left Outer Join.

In a report when I fetch records in the secondary table, I only fetch records matching the primary table and not those that are in the primary and not matched in the secondary.
 
I am using MS Access and Crystal Report 9 with latest patches.
 
I have read the knowledge base and understand that outer joins are non standard with regard to SQL however I would have thought MS Access would not be a problem.
 
Any clues.  I do need an outer join since I will be using sub reports with side by side alignment of information.
 
Thanks in anticipation.
 
0
Comment
Question by:PSernz
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8013483
A Left Outer Join by definition will pull all the records from the left side, whether or not there are any matching records on the right side of the join.

A RIGHT outer join will return all the records in the second table and only those in the first table which have a match in the second.

Left and Right are generally defined by first table mentioned in a join statement.

An inner join will return rows from both tables, but only where there is a match.  There really is no left inner or right inner join - there is only an inner join.

Hope that helps clarify.

You can change a link type by right clicking on it in the link editor in Crystal and selecting Properties.

DRRYAN3
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8013722
Excellent descriptions.

>>In a report when I fetch records in the secondary table, I only fetch records matching the primary table and not those that are in the primary and not matched in the secondary.

What do you mean?  If you are fetching records from the secondary table you can't fetch records that aren't there.

mlmcc
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8013747
He is apparently describing a right outer join, not a left outer join.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:PSernz
ID: 8015262
DRRYAN3,

My question related to a LEFT OUTER JOIN not a LEFT INNER JOIN.  The Primary table is on the LEFT and the secondary table is on the RIGHT.

MIMCC,

Refer to Help on the CR LEFT OUTER JOIN.  Such a join should return both secondary table rows matching the primary key as well as NULL values reflecting secondary table columnns that do not match primary table keys.

This does not appear to happen when you use select logic on the secondary table with Left Outer Join operating.  The Select appears to override the return of NULL rows associated with non matched secondary keys.
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8016776
"In a report when I fetch records in the secondary table, I only fetch records matching the primary table and not those that are in the primary and not matched in the secondary" - PSernz

You are not describing a left outer join with that sentence.  You are describing a Right Outer Join.

"Such a join should return both secondary table rows matching the primary key as well as NULL values reflecting secondary table columnns that do not match primary table keys"

You are correctly describing a left outer join in that sentence.

As I read your text again, I cannot find what it is that you are asking.  There is no question there.  What is not working?

DRRYAN3
0
 

Author Comment

by:PSernz
ID: 8022375
DRRYAN3,

From further investigation, it seems that CR does not return true Left Outer Join if the SQL incorporates a WHERE clause on data in the secondary or Right table.
i.e. the returned table contains pnly those records from the Left Table that have found a match in the right table.

When you remove the WHERE clause, the Left Outer Join works i.e. the returned table contains all records from the Left Table even though match is not found in all records in the Right Table.

From this observation it would seem that the WHERE clause effectively nullifies the Left Outer Join directive.

The question is ...  is this to be expected from an SQL directive or is this a querk of MS Access with CR.
 
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8022744
Your observation is correct, and according to Crystal Decisions, is to be expected when you access Access using native drivers.  The direct answer to your question is that it is not normal SQL behavior but is a quirk of Access (or any other PC style database accessed natively).

See http://support.crystaldecisions.com/library/kbase/articles/c2009072.asp for all the details.

In short, if you want normal SQL behavior, you will have to use an ODBC driver.  Otherwise, Crystal Reports is scanning the first table then matching up the records from the second table on another pass.

DRRYAN3
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8022745
Your observation is correct, and according to Crystal Decisions, is to be expected when you access Access using native drivers.  The direct answer to your question is that it is not normal SQL behavior but is a quirk of Access (or any other PC style database accessed natively).

See http://support.crystaldecisions.com/library/kbase/articles/c2009072.asp for all the details.

In short, if you want normal SQL behavior, you will have to use an ODBC driver.  Otherwise, Crystal Reports is scanning the first table then matching up the records from the second table on another pass.

DRRYAN3
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8022955
Including a where clause on the right table should limit the records in the left table to those that match unless you consider NULL matches everything.

For instance a database of clients and orders linked on order number

Select SOME FIELDS FROM
Clients LEFT OUTER JOIN Orders ON Client.ClientID = Order.CLientID

This will return clients who have never placed an order

But if I Add a where clause like

Where Order.total > 100.00

I should get all clients and their order information where the order is greater than 100.00  Are you saying I should also get all clients who have never order anything since they will have nothing in the order table?

If that is the case then I guess the where clause should be dependent on the master table.

mlmcc
0
 

Author Comment

by:PSernz
ID: 8022982
DRRYAN3,

Perhaps I should just identify what I want to do..

To simplify..
I have Table A with a Column of Name.  This is the Primary (Master) Table with all Names.

I have Table B with Columns of Name,Amount,Date.  This is the Secondary Table with only a selection of Names found in Table A.

I need to generate a report with all records in Table A and Amounts from Table B where available over particular ranges of B.Dates.

Table A.Name is joined to Table B.Name with a Left Outer Join.

The SQL Query is :
SELECT Name,Amount,Date FROM A LEFT OUTER JOIN B ON A.Name = B.Name WHERE B.Date >= [start date] AND B.Date < [end date] ORDER BY A.Name

How do I accomplish such a join with selection in CR.

As explained it would seem that the WHERE clause to select only particular ranges of dates from Table B effectively removes the records from A that do not satisfy the WHERE clause and therefore I do not get all records from A in the result.
0
 

Author Comment

by:PSernz
ID: 8023012
Thanks guys. It seems our responses have missed each other on the network.

DRRYAN3,
This seems to be the answer unfortunately.  Is there another way around the problem.

MLMCC,
Unfortunately I can't do the WHERE on the Master or Left Table.  See my last comment.

0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8023037
I don't use Access that much anymore.  Can't you base your report on an Access Query?  Create the query in Access and test it there.  Then, in Crystal Reports you no longer have a join problem since the query appears as a single table.

DRRYAN3
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8023142
My point wasn't to do the where clause on the left table but to try to say that in effect putting the where clause on the right table makes it the master and you essentially have a right outer join.

You may be able to build th equery in Access and then copy the SQL to CR.

mlmcc
0
 

Author Comment

by:PSernz
ID: 8023217
DRRYAN3,
I would prefer to keep the report generic so that the database can be moved to Microsoft SQL for the future.

mlmcc,
How do you copy an SQL query to CR ?

0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8025494
PSernz

If you ever choose to move the report to SQL Server, you will almost certainly find that you cannot report off the native tables, but will need views and stored procedured (similar to Access saved queries, but better).

Also, did you ever try to use an ODBC driver instead of the native driver for your report?

DRRYAN3
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 8025497
that should be stored procedures, not procedured
0
 

Author Comment

by:PSernz
ID: 8028939
DRRYAN3,mlmcc,

Thanks for your help.

I believe I have now found a workaround for the issue.

The Left Outer Join is in fact working.  If the selection logic within CR uses the ISNull function (NULL in SQL) to include those records with unmatched or NULL secondary columns, the SQL returns all records in the Primary Table.
Seems simple when you look back on the problem but thats usually the case isn't it.

Cheers.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 11825383
PAQed, with points refunded (50)

modulo
Community Support Moderator
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question