| Author |
 Topic  |
|
|
allan8964
Posting Yak Master
202 Posts |
 Posted - 12/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 01:53:24
|
select t1.* from table1 t1
inner join table2 t2 on t1.Code like '%' +t2.Model+ '%' |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 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
202 Posts |
Posted - 12/09/2012 : 10:13:39
|
| thanks sodeep. i tried this, no error but returns either. |
|
|
|
allan8964
Posting Yak Master
202 Posts |
Posted - 12/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 11:05:42
|
| Yes. Did you try mine? |
|
|
|
allan8964
Posting Yak Master
202 Posts |
Posted - 12/09/2012 : 11:10:25
|
| did I tell you it did not work? |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 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
202 Posts |
Posted - 12/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 11:21:21
|
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 |
Edited by - sodeep on 12/09/2012 11:22:41 |
|
|
|
allan8964
Posting Yak Master
202 Posts |
Posted - 12/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 14:07:55
|
| welcome |
|
|
| |
Topic |
|
how to solve this in inner join ?
