Left join limitation 2

[dreman]

Using CR 8.5 with ODBC drive (Sql)
this should be very simple yet it is not working.
I do think it is a limitation of Crystal Report.
i have 2 sql table linked:
customer->sales_history linked by customer_id
Parameter are based upon:
sls.customer (ie ABC)
date.sales_history (ie 1/1/1910 to today's date, to cover all sales)

this is what I am trying to resolve.
List all customer for a particular sls whether we sold to them or not.
I've used left join but it only listing the customer if they have records in sales_history.
How can I list customers if they do not have sales or record in the other table.
Any advice, thank you.
dré

 

[bjrollet]

Use a RIGHT OUTER JOIN. This will return all customers, regardless if they purchased something or not.

ex.

Select o.orderID, c.custID from orders o right outer join customer c on c.intid=o.intcustomer

this will return all orders and all customers. This working out thing isn't working out.

 

[synapsevampire]

A Left Outer works...

Here's a basic overview:

Left Join is used to create a left outer join which will include all the records from the first table plus any matching records from the second table.
::= FROM table1

JOIN table2 ON table1.field1 = table2.field2

Right Join is used to create a right outer join which will include all the records from the second table plus any matching from the first table.
::= FROM table1

JOIN table2 ON table1.field1 = table2.field2

So this is dependent upon which table is your first, post the SQL (Database-Show SQL Query).

I'd guess that you have the Left Outer set in the wrong direction.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com​

 

[kenhamady]

Are you putting a date range criteria here?

If you linke from Customers to Sales History using an outer join, and then you put a criteria on the date field in the sales history table, you have canceled the effect of the outer join. This is not a Crystal limitation, but a SQL behavior. When you use an outer join, you can't put criteria on the outer table. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dreman]

Ken:
I think you are right. There is a limitation on table 2.
and thanks for synapsovampire for explaining left and right outer.
However I will try to tweak Sql and add null(date) to see if I can bring all customers from table1.
Thank you.
dré

 

[Naith]

You don't have to do that Dre.

Just switch the relationships between the tables.

Right Outer from Sales History to Customer, with the date clause on Sales History.

Naith

 

[kenhamady]

Naith,

I just tested your recommendation in MS Access, and I lose the customers with no transactions even though I did a right outer join. Does this work in some other database like SQL Server or Oracle? That would surprise me. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Naith]

It sounds like you're describing a Left Outer Join from Sales to Customer, or a Right Outer Join from Customer to Sales.

In Sybase12, following:

Sales -> Customer(+)

with a condition set on the Sales equivalent, I get full customers, and corresponding sales (and an extra set of nulls where the condition isn't met in Sales).

Naith

 

[dreman]

Naith:
I did witch the table and used right outer but it still not working, table1 uses date parameter and table2 has some parameter based on salesperson.
Also tried with null(date) it still didn't work. It seemed that whenever there is a parameter applied to table2 it won't work, if I take parameter from table2 it will work.
I am still trying to find a solution.
Thanks,
dré

 

[dreman]

Still having a problem:
I am using:

Table1 right Join table2 on table1.cust=table2.cust

however from table1 I have parameter for date range and from table2 I have a parameter for salesperon.ID
What I am trying to do is to get all customer for salesperson.id wether they have sales or not.
According to the right Join it should return all table2.customer_id for that salesperson but it is not.
If I take range of date then it is fine and it is taking a lot longer (which is obvious).
Any advice,
Thank you.
dré

 

[Naith]

Dre,

I thought you were only putting a date criteria on one of the tables.

I interpreted your initial post:

i have 2 sql table linked:
customer->sales_history linked by customer_id
Parameter are based upon:
sls.customer (ie ABC)
date.sales_history (ie 1/1/1910 to today's date, to cover all sales)

to mean the parameters were on Sales_History, and that sls.customer was a typo meaning Sales_History.Customer, due to you only mentioning two tables in the link.

If you're applying a condition through parameters to both tables, then that's why you're right-outer join isn't really doing the do. The submissive table in the join can't have any conditions on it whatsoever for the link to be adhered to.

Naith

 

[kenhamady]

Naith,

I did exactly what you described. Apparently, it doesn't work the same way in MS Access, and it may not work the same way in other platforms.

Dreman,

What is your database platform. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[lisaab4]

Hi, I also have a querey with joins and would like to know if a change in the db driver being used for the report will affect the join syntax. I am trying to emulate this and would like to know what I should look for and where to determine the answer. Thanks.

 

[dreman]

Ken:
Platform is SQL 2000.
I think Naith mentioned as well as you did ken that since parameter are applied to both table right or left join will not work.
Thanks anyways.
Dré

 

[dreman]

Naith or Ken:
Then how would i list all customer for a particular salesperson whether the customer has sales or not.
This is very critical from a company prespective to see who are they selling for or not.
Any suggestions ?
Please help
Thank you.
dré

 

[endoflux]

Okay, so I clicked into this thread to learn a bit more on relationships...and suddenly there's a whole new world of complexity to creating my reports, to the effect that I may need to restructure my database to get the results I actually want a lot of the time...

Question is, what's the best way to go about learning Advanced DB stuff, including relationships, analysis, etc-- something like a SAD course would provide? I'm located in Minneapolis, if someone knows of a good class, etc. While i know quite a bit on databases and whatnot, I think I could benefit from learning things like the limitations on parameters/selection criteria for slave tables

 

[dreman]

Endoflux:
Please open a new thread for you question.
As to learn about advanced DB or SQL is unfortunately by experimenting a lot of error/trial. You can strt learning by grabbing books such as learn SQL in 24 hours, or in one week. But to become proficient you must physically work with it.
Dré

 

[dreman]

Well very interesting:
We were able to solve the problem via sql itself. Now comes the question how to resolve it in Crystal.
The way we resolve it is by putting the Date parameter range in the FROM clause in SQL and not in the WHERE clause.
Now how would I be able to implement the SQL query within Crystal report.
Please advice,
Thank you.

 

[kenhamady]

If you have SQL you like, you can write a view or Stored Procedure that stores this SQL in the database, and report from that.

In Crystal you could use a subreport to look up the sales, rather than linking the table. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[infomania]

I got to the end of this thread hoping for the answer I need. Dreman... did you ever figure this out? I have the exact same problem, using a date parameter in the TO table. I gather this cancels the action (intention) of the Left Outer Join. You mentioned a solution was to put the parameter into the FROM SQL statement. Can you provide details? The date range I am using would be an input parameter at the time the report was refreshed.

Thanks.

Sleepless in Savannah