Crystal Report excel file made cartesian join product

In Crystal Report 2011 I have problem connecting SQL Server table "Supplier" with excel file "Product_Info". I am getting cartesian product.
In excel file  Product_Info I have several fields one of them is field Supplier_Number and it is number type field.
In crystal report I have table Supplier that has Supplier_Num field as text type field.
To connect this table with the excel file by those two fields I had to add “”- space in Excel field Supplier-_Numer at end of the field, so when imported in crystal it would be text type field. I did that change. Then in crystal report I made new connection : CreateNewConnection\Access./Excel(DAO)  and brought excel file in.
Then I linked them and they should go as one to many or Left outer join(Suppliier.Supplier_Num  To Product_Info.Supplier_Number).
In crystal I can see that in Product_Info Supplier_Numer  is text type filed,Everting looks ok.

However what is happening I am getting Cartesian product between those  too tables.
 And how to get proper link?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DID you join the tables in the link expert?  If not then you will get a cross join or Cartesian product


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Of course I join them in link expert.
From what you've said, I don't know how you're getting those results.  I would go to Database > "Show SQL Query" and check the query that CR is actually using.

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

TarasAuthor Commented:
this is how it  looks in SQL Query I changed some names but structure is the same.Beside Supplier table I have Invoice and ShipInfo tables in report.

Select “Supplier.Address”,ShipInfo.CustomerNumber,Supplier.Supplier_Num
(“Datawarehouse”.”dbo”.”Invoice” INNER JOIN “Datawarhouse”.”dbo”.ShipInfo””ShipInfo” ON “Invoice”.”InvoiceShipInfoKey” = “ShipInfo”.”Ship_Info_Key”) INNER JOIN “Datawarehouse”.”dbo”.”Supplier””Supplier” ON “Invoice”.”SupplierKey” = “Supplier”.”SupplierKey”
Where “ShipInfo”.”CustomerNumber” = 1050
EXTERNAL JOIN Supplier.Supplier_Num= {?C:\User\Public\Documents\ExcelDataFiles\Supplier_Info.xls:’Sheet1’.Supplier_Number}

From ‘Sheet1’ ‘Sheet1’
Where ‘Sheet1’.’Supplier_Number ‘= {?sqldb\Production:Supplier.Supplier_Num}
I don't see anything wrong, but I haven't used multiple datasources much, so maybe I'm missing something.

 I was thinking about this earlier.  Are you sure that you're getting a cross-join?  Maybe you're just getting some extra records?

 IAC, I'd try simplifying things, to try to identify the source of the problem.  First, remove the Excel file from the report (since you think that's causing the problem) and see if you still get "extra" records.

 If removing the Excel file doesn't fix the problem, go back to the original report, with the Excel file included, and try removing Invoice and ShipInfo instead, and see if you still get "extra" records.

 On a side note, if you're only using ShipInfo.CustomerNumber, do you really need ShipInfo?  I would think that Invoice would include the customer #, in which case, it doesn't seem like you need ShipInfo.  But maybe invoice doesn't include it, or maybe it's a different "customer #".

I have seen the use of 2 sources and the SQL shows it.

Have you considered using a subreport for the Excel file?

TarasAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.