left join error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

left join error

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2007-04-27 : 16:02:33

I am trying to do a left join from one table a to table B on a column CEO_ID which is of datatype varchar(5) in table A and of datatye nvarchar(20) in table b.The resultset gives me all the rows that are in both the tables except a few..So when I looked at the records that are missing, it is stored as 00392 in table A and 392 in table b and that is the reason it is not showing that record in the result.How can I show these records?

Query
Select p.CEO_ID,p.Part_ID,ct.CEO_ID__c
from
Contact ct
left join
Main5.dbo.Part p
on
p.CEO_ID = Convert(varchar(5),ct.CEO_ID__c) collate database_default
where
ct.CEO_ID__c = 392

ResultSet

CEO_ID CEO_ID__c
------ ---------
NULL 392

Please help me !

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-27 : 16:52:36

Questions:
1) Are all the values stored at numbers?
2) Are all the values in table A (Contact?) Stored as 00392 or are some 00392 and others 398?

If question 1 is Yes, they are all stored as numbers then I'd convert them to numbers and compare. Otherwise you might try this?:

Select p.CEO_ID,p.Part_ID,ct.CEO_ID__c
from
Contact ct
left join
Main5.dbo.Part p
on
RIGHT('000' + p.CEO_ID, 5) = Convert(varchar(5),ct.CEO_ID__c) collate database_default
where 
ct.CEO_ID__c = '00392'

Cheers,

-Ryan

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-04-30 : 05:00:46

I'd also recomment fixing your data up to use the correct type put a constraint on it (if it makes sense to do so).

What you have cannot use an index to do the join so I hope it's not going to be a big table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources