sub query result in like operator

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

SQL Server 2000 Forums

SQL Server Development (2000)

sub query result in like operator

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-19 : 03:56:59

Hi,

I have a child table which has all the file extensions that should be ignored.

Usually when you have a single value that needs to be used with a like operaror we say

select * from tbl where column not like '%.asp'

But I want a result of a subquery

select * from tbl where column not like (select * from childtable)

How do i write in a query.
Hope I am clear to you.

Thanks in advance
Babli

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-19 : 04:04:48

How about this?

Select *
From tbl t1
Where not  exists(select * from childtable t2 where t2.column like '%' + t1.column )

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-19 : 04:21:05

No its not taking it

The above query's result is having the data that should ignored (when we say like "%')

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-19 : 04:26:14

Hi,

I would like to be more clear.

MainTable
Col1
http://sitename/default.aspx
http://sitename/calendar.gif
http://sitename/article.doc

ChildTable - table name
Extensions - column name
.aspx
.gif

The output of my query should be
http://sitename/article.doc

I tried select * from Maintable where Col1 not like (select * from ChildTable) but no luck

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 04:26:37

Harsh suggestion worked just fine, if you interchanged the comparison in the subquery.

-- prepare sample data
declare	@tbl table (filename varchar(100))

insert	@tbl
select	'peso.txt' union all
select	'peso.asp' union all
select	'harsh.asp'

declare	@childtable table (extension varchar(10))

insert	@childtable
select	'.txt' union all
select	'.mpg'

select	t.*
from	@tbl as t
where	not exists (select null from @childtable as ct where t.filename like '%' + ct.extension)

Peter Larsson
Helsingborg, Sweden

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-19 : 04:31:17

Yes its silly mistake.

I should have interchanged the columns.

Working very fine

Thank you so much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 04:34:25

Well. I hope you have learned something here.
When given suggestions, try to understand them and figure out why they are working and how they are working.
Not only will you improve your skills, but also train to see these small errors and correct them yourself.

Good luck!

Peter Larsson
Helsingborg, Sweden

PrakashK
Starting Member

1 Post

Posted - 2011-01-28 : 02:03:04

Hi All,

I want a similar query that you are discussing above.

I have 2 tables namely
1. Location.
2. Contact.

For each location_id in location table say 'asdf' one contact_id 'asdf' exists in contact table with same name as location_id.In addition to that there are some more cotacts where they will be appended '-001','-002'. so there are multiple contacts for same location

LOcation_id - asdf Contact -asdf
asdf-001
asdf-002

now I want all the contact_ids whic are like location_id.

Prakash

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources