Avatar of Mchallinor
MchallinorFlag for United Kingdom of Great Britain and Northern Ireland asked on

Please help with Left Join in Crystal Reports

I would like to show transactions for all customers and the report should show all customers including those with no records.

I need some suggestions why my Left Join is not working.  
I have attached an image of the links.

Thanks
Mchallinor

Crystal Reports XI
Database = SQL via ODBC connection
links.JPG
DB Reporting ToolsCrystal Reports

Avatar of undefined
Last Comment
Kurt Reinhardt

8/22/2022 - Mon
peter57r

Are you doing any record selection?

(Post the SQL for the report)
ASKER
Mchallinor

Yes:

not ({TProduct.Sales_Cost_Centre} in ["103", "105", "140", "160", "180"]) and
{TProduct.Product_Type} = "f" and
{TInvoice.Invoice_Date} >= DateTime (2007, 01, 01, 00, 00, 00) and
month({TInvoice.Invoice_Date})=month (today) or if day(today)<7 then month({TInvoice.Invoice_Date})=month (today)-1 and
{Customer.Company} = 1
ASKER
Mchallinor

I have modified the Record Selection to only show Invoices for yesterday.

This the SQL Query:

 SELECT "TInvoice"."Invoice", "TInvoice"."Invoice_Date", "TInvoice"."Currency", "TInvoice"."Currency_Rate", "TProduct"."Product", "Brand"."Description", "Product_Group"."Description", "Customer"."Name", "TInvoice"."Base_Invoice_Value", "Area"."Area"
 FROM   ("BOOMLive"."dbo"."Area" "Area" INNER JOIN "BOOMLive"."dbo"."Customer" "Customer" ON ("Area"."Company"="Customer"."Company") AND ("Area"."Area"="Customer"."Area")) LEFT OUTER JOIN ((("BOOMLive"."dbo"."TProduct" "TProduct" INNER JOIN "BOOMLive"."dbo"."TInvoice" "TInvoice" ON ((((("TProduct"."Company"="TInvoice"."Company") AND ("TProduct"."Product"="TInvoice"."Product")) AND ("TProduct"."Matrix_Code_1"="TInvoice"."Matrix_Code_1")) AND ("TProduct"."Matrix_Code_2"="TInvoice"."Matrix_Code_2")) AND ("TProduct"."Matrix_Code_3"="TInvoice"."Matrix_Code_3")) AND ("TProduct"."Matrix_Code_4"="TInvoice"."Matrix_Code_4")) INNER JOIN "BOOMLive"."dbo"."Brand" "Brand" ON ("TProduct"."Company"="Brand"."Company") AND ("TProduct"."Brand"="Brand"."Brand")) INNER JOIN "BOOMLive"."dbo"."Product_Group" "Product_Group" ON ("TProduct"."Company"="Product_Group"."Company") AND ("TProduct"."Product_Group"="Product_Group"."Product_Group")) ON ("Customer"."Customer"="TInvoice"."Customer") AND ("Customer"."Company"="TInvoice"."Company")
 WHERE  ("TInvoice"."Invoice_Date">={ts '2009-01-22 00:00:00'} AND "TInvoice"."Invoice_Date"<{ts '2009-01-23 00:00:00'})

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Mchallinor

Dear peter,  I think I am showing my lack of experience on this topic.

After stripping back the report to basics, I am starting to understand why the Left Join is not working. It's because I have applied a selection criteria on the TInvoice table.

Therefore I need some suggestions how do I display on the report ALL the customers but only Invoice transactions for the last 7 days.  Still showing all the customer names and values of 0.00 for the customers that did not product any invoice transaction in the last 7 days.

Please advise.
peter57r

As you have discovered you cannot do selection from the right hand side of a Left join.  If you do, it ereberts to an inner join.

The way through this is to create a query on the right-side table first, which does the selection, , and then join the left-side table to the query.

So in a simple situation you might have...

Select tbl1.* , q1.* from tbl1
Left join
(Select * from tbl2 where field1 = 999) as q1
On tbl1.idfield = q1.idfield


and NOT...
Select tbl1.* , tbl2.* from tbl1
Left join tbl2
On tbl1.idfield = tbl2.idfield
where tbl2.field1 = 999

Mike McCracken

You can also do it this way

Select tbl1.* , tbl2.* from tbl1
Left join tbl2
On tbl1.idfield = tbl2.idfield
AND  tbl2.field1 = 999

mlmcc
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kurt Reinhardt

As the others have stated, applying a filter to a table that has been left joined implicitly turns the join into an Inner Join.  This is because you remove all the rows that would otherwise be NULL when you explicitly filter against actual values.  After all, if  you specify that a field in the left joined table has a value, it obviously can't be NULL.

Knowing this, you can simply do a check for NULLs in the table that has been left joined, something like this.  Here's a high level example, assuming Table1 is the left table and Table 2 is the right table to which you've left outer joined:



(
IsNull({Table2.Field1}
Or {Table2.Field2} = 'ABC123'
)
And {Table1.Field1} = 'B-I-N-G-O'
 
//In this example, stipulating that a field from the left joined table can be NULL allows both the NULL records from the left joined table
//and the other records that have been explicitly filtered
//due to the manner in which Crystal Reports handles NULL checking in the selection criteria, I put the NULL check first
//please note, I've also encapsulated the entire filter against the left joined table in parentheses, so that it's evaluated as one statement

Open in new window

ASKER
Mchallinor

Thanks rhinok , mlmcc and peter.

I completely understand the concept how you can't apply a left join to a table you are applying a selection criteria too.

What I don't understand is how to get around this.  You can given me example SQL code.  And I can understand your basic examples.  But when I am designing a report with Crystal Reports I have no idea how to make the database links with SQL Code.  I only know how to link the fields using the "Database Expert".  I've tried all sorts of things, like putting linking from the transactions table to the customer table and the other way around and applying Left Joins , Right Joins and Full outer Joins.

Am I wasting my time with the "Database Expert" in Crystal Reports and there's another way to create my links????

Thanks in advance.
ASKER CERTIFIED SOLUTION
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.