how to solve this in inner join ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to solve this in inner join ?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2012-12-09 : 00:46:43

Hi there,

I have a select statement using inner join, but joined tables do not have consistent data. See following:

table1 has column of Code
Code | ...
ABC NXY |
ABC |
ABC NXY |...

table2 has column of Model
Model |
ABC
CBC
DDB......

Now I have select statement:

select * from table1 t1
inner join table2 t2 on t2.Model = t1.Code
where ....

Actually I need all records which have ABC in Code column. Can the aboe statement rule out all the records which contain 'ABC'? Seems like you can't use LIKE in inner join statement. How can I include all the records which contains 'ABC' in table1, no matter it's 'ABC NXY' or 'ABC NXY' or ... Thanks in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 01:53:24

[code]select t1.* from table1 t1
inner join table2 t2 on t1.Code like '%' +t2.Model+ '%'[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 10:12:10

If it is consistent starting with ABC

select * from table1 t1
inner join @t2 table2 on t2.Model = Left(t1.Code,3)

allan8964
Posting Yak Master

249 Posts

Posted - 2012-12-09 : 10:13:39

thanks sodeep. i tried this, no error but returns either.

allan8964
Posting Yak Master

249 Posts

Posted - 2012-12-09 : 10:46:57

well, in that case i don't need join table2. I can use

where code like '%ABC%'

But I need get ModelId from table2. That's why I use join. The issue here is: does like work in join statement?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 11:05:42

Yes. Did you try mine?

allan8964
Posting Yak Master

249 Posts

Posted - 2012-12-09 : 11:10:25

did I tell you it did not work?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 11:13:46

What does this give you?

select t1.*,t2.ModelId from table1 t1
inner join table2 t2 on t1.Code like '%' +t2.Model+ '%'

If you question is different, post with clear example and expected so we can help

allan8964
Posting Yak Master

249 Posts

Posted - 2012-12-09 : 11:14:04

I am sorry, sodeep. Actually I typed it wrong in the first post. Table1 Code of data look like this:

ABC NXY
ABC
TYG ABC
GTR ABC UYT

Situation is ABC may in any of te three positions. That's the hard part. If just in left, that's easy. Sorry for typo.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 11:21:21

[code]Declare @T Table(Code Varchar(20))

Insert into @T
Select 'ABC NXY' union all
Select 'ABC' union all
Select 'TYG ABC' union all
Select 'GTR ABC UYT'

Declare @S Table(ModelId Varchar(10))

Insert into @S
Select 'ABC' union all
Select 'CBC' union all
Select 'DDB'

select t1.*,t2.ModelId from @T t1
inner join @S t2 on t1.Code like '%' + t2.ModelId+ '%'

Code ModelId
ABC NXY ABC
ABC ABC
TYG ABC ABC
GTR ABC UYT ABC[/code]

allan8964
Posting Yak Master

249 Posts

Posted - 2012-12-09 : 11:43:17

great! sodeep, It's perfect. I figured out why it did not work with my codes ... some other joins affected it ... now fixed. Thank you so much!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 14:07:55

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources