Using LIKE for multiple conditions

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 2005 Forums

Transact-SQL (2005)

Using LIKE for multiple conditions

Author

Topic

Cattrah
Starting Member

13 Posts

Posted - 2008-07-07 : 12:40:38

I need to do a quick search on a column that indicates the parent-child lineage of the record for a set of values. These values are in another table. Is there a way to join in those values into a like statement or do I need to use a loop/cursor to create a dynamic SQL statement to execute? For example:

User has two items he would like to watch, these are in table A:
80608
80462

Table B contains all the items. I need to find any items that have those two the user is watching in their lineage. so anything where Lineage LIKE '%.80462.%'

If break it up like this:

SELECT * FROM Equipment WHERE Lineage like '%.80462.%' OR Lineage like '%.80608.%'

I get the results I want, but I'd like to do it in a way where I select the LIKE items from table A instead of hard coding it in or dynamically building the query, is there a way?

The concept is like this:

SELECT * FROM Equipment WHERE Lineage like '%.' + (SELECT EquipmentID FROM TableA WHERE User=1) + '.%'

Which I know that won't work, but it's like what I want to do. Any ideas?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-07 : 12:49:02

Something like this:

SELECT E.* 
FROM Equipment E join TableA A
On E.Lineage like '%.' + A.EquipmentID + '.%'
Where A.User=1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Cattrah
Starting Member

13 Posts

Posted - 2008-07-07 : 12:50:49

Holy cow, I'm so lame. I swear the moment I post something I figure out the answer. Hello join!

SELECT E.ID FROM Equipment E INNER JOIN EquipmentWatch W on E.Lineage LIKE '%.' + convert(varchar(50), W.EquipmentID) + '.%' AND W.User=1

Thanks for looking, lets all snicker at the pregnant lady now :-)

Cattrah
Starting Member

13 Posts

Posted - 2008-07-07 : 12:51:41

thanks harsh! Exactly what I figured out :-)

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-07-07 : 14:02:17

quote:
Originally posted by Cattrah

Holy cow, I'm so lame. I swear the moment I post something I figure out the answer. Hello join!

SELECT E.ID FROM Equipment E INNER JOIN EquipmentWatch W on E.Lineage LIKE '%.' + convert(varchar(50), W.EquipmentID) + '.%' AND W.User=1

Thanks for looking, lets all snicker at the pregnant lady now :-)

Pregnant women are BEAUTIFUL so I would never snicker at them. Amazing how as soon as you post, the little light bulb goes off and you have a "DOH" moment. Bottom line, as long as you get the answer you need, what's the difference in how you got it???

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources