February 16, 2007 at 2:35 am
Guys hi,
i have a problem that i would apporeciate any thoughts, and your help please.
I have a table Leads (A) that contains data loaded from an excel file.
I want to get all common records by joining this table with a table Leadbase (B).
My problem is that the join does not work as it supposedly should do. I do not get all the records (2700 records) but only a portion of them (2200 records), allthough both tables contain identical rows.
The query goes
SELECT
DISTINCT
A.CompanyName
FROM Leads A
inner JOIN
---------------------- LeadBase - LeadAddressBase VIEW --------------------
(SELECT
A.CompanyName
FROM dbo.LeadBase B )AS C
---------------------------------------------------------------------------
ON LTRIM(RTRIM(ISNULL(A.CompanyName,'-'))) = LTRIM(RTRIM(ISNULL(C.CompanyName,'-')))
The problem is that I missing 500 records in my results. I believe that there is a collation problem that prohibits the correct joining of the table (although both tables have the same collation). Both tables are in the same server with collation GREEK_CI_AS and they are both in the same database with collation LATIN1_GENERAL_CI_AS.
I think the problem exists because the first table table has loaded records from excel, through a DTS loading procedure.
What do you think? how can i resolve this?
Thank you,
February 16, 2007 at 2:54 am
Try this .............
Select DISTINCT
A.CompanyName
FROM Leads A , dbo.LeadBase B
where LTRIM(RTRIM(ISNULL(A.CompanyName,'-'))) = LTRIM(RTRIM(ISNULL(C.CompanyName,'-')))
check it how many rows u got......
February 16, 2007 at 3:09 am
I used Where instead of Inner Join but i got 2200 records again.
Any other idea?
February 16, 2007 at 3:29 am
you are selecting distinct company name,
Just check your data. It might be possible that you have same company name in multiple rows.
February 16, 2007 at 3:47 am
I did it. The multiple rows are only 10.
There is no problem with the query. The problem is focused on data format (as far as I can understand).
February 16, 2007 at 4:05 am
Check the output of this query
SELECT
DISTINCT A.CompanyName
FROM
dbo
.Leads L
INNER
JOIN dbo.LeadBase LB ON L.CompanyName <> LB.CompanyName
AND
SELECT DISTINCT A.CompanyName
FROM
dbo
.Leads L
INNER
JOIN dbo.LeadBase LB ON RTRIM(LTRIM(ISNULL(L.CompanyName,''))) <> RTRIM(LTRIM(ISNULL(LB.CompanyName,'')))
Thanks
OR USE A FULL OUTER JOIN to check which rows are missing.
Prasad Bhogadi
www.inforaise.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply