?
Solved

Left Outer Join

Posted on 2003-02-24
19
Medium Priority
?
643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +1
19 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

743 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