By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

is there anysql equivalent to inner join ins on ins.address like '%a%'

100+
P: 232
SELECT [TOWN].*, institute.address
FROM [TOWN] inner join institute on institute.address like '%[TOWN].town%'
Dec 5 '09 #1
Share this Question
Share on Google+
1 Reply


nbiswas
100+
P: 149
Instead of like
'%[TOWN].town%'
try
like '%' + [TOWN].town + '%'


e.g.

Let's create some sample data

Expand|Select|Wrap|Line Numbers
  1. declare @town table(tid int, tname varchar(50))
  2. declare @institute table(tid int,address varchar(50))
  3.  
  4.  
  5. insert into @town 
  6.     select 1,'town1' union all select 2,'town2' union all
  7.     select 3,'town3' union all select 4,'town4' 
  8. insert into @institute 
  9.     select 1,'town1 address1' union all select 1, 'town1 address2' union all
  10.     select 2,'town2 address3' union all select 3, 'town3 address4' union all
  11.     select 4,'town4 address5' union all select 4, 'address6' 
Query:

Expand|Select|Wrap|Line Numbers
  1. select t.*,i.address
  2. from @town t
  3. inner join @institute i
  4. on t.tid = i.tid 
  5. where i.address like '%' + t.tname + '%'
Output:

Expand|Select|Wrap|Line Numbers
  1. tid    tname    address
  2. 1    town1    town1 address1
  3. 1    town1    town1 address2
  4. 2    town2    town2 address3
  5. 3    town3    town3 address4
  6. 4    town4    town4 address5
Hope this helps
Dec 7 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.