Matching multiple columns on n search strings

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)

Matching multiple columns on n search strings

Author

Topic

taylordave
Starting Member

6 Posts

Posted - 2007-11-09 : 08:24:50

Hi

I'm trying to find rows where fields match a number of input strings. The SentenceToTable function splits a string by spaces into a table.

The following SQL half works, it returns all rows where either [CarName] or [LocationName] match 'clap' or 'van'. What I want to get are only those rows that match both 'clap' and 'van'.
[CODE]
SELECT c.[CarName]
,l.[LocationName]
FROM [dbo].[Cars] c INNER JOIN
[dbo].[CarsInLocations] cil ON c.Id = cil.CarId INNER JOIN
[dbo].[Locations] l ON cil.locationid = l.Id INNER JOIN
dbo.uf_SentenceToTable('clap van') words ON
([CarName] Like '%'+words.[string]+'%' OR
[LocationName] Like '%'+words.[string]+'%')
[/CODE]

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-09 : 08:39:09

group by c.[CarName],l.[LocationName]
having count(*) = 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

taylordave
Starting Member

6 Posts

Posted - 2007-11-09 : 08:42:28

One approach I wanted to try was adding a WHERE clause:

WHERE [CarName]+[LocationName] like ALL ( select '%'+words2.[string]+'%' FROM dbo.uf_SentenceToTable('clap van') words2 )

But for some reason the ALL keyword only uses standard comparison operators.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 08:47:00

[code]SELECT c.[CarName],
l.[LocationName]
FROM [dbo].[sc4_cars] as c
inner join [dbo].[sc4_carsinlocations] as cil ON cil.CarId = c.Id
INNER JOIN [dbo].[sc4_locations] as l ON l.Id = cil.locationid
CROSS APPLY dbo.uf_SentenceToTable('clap van') as words
WHERE [CarName] Like '%'+words.[string]+'%'
OR [LocationName] Like '%'+words.[string]+'%'[/code]

E 12°55'05.25"
N 56°04'39.16"

taylordave
Starting Member

6 Posts

Posted - 2007-11-09 : 10:00:36

quote:

Originally posted by Peso

SELECT		c.[CarName],
		l.[LocationName]
FROM		[dbo].[sc4_cars] as c
inner join	[dbo].[sc4_carsinlocations] as cil ON cil.CarId = c.Id
INNER JOIN	[dbo].[sc4_locations] as l ON l.Id = cil.locationid
CROSS APPLY	dbo.uf_SentenceToTable('clap van') as words 
WHERE		[CarName] Like '%'+words.[string]+'%'
		OR [LocationName] Like '%'+words.[string]+'%'

Thanks Peso, I tried that but it returned the same results as my original query:


CarName    LocationName
-------    -----------------------
Oscar      Clapham Old Town
Edna       Clapham South
Liam       Clapham - Voltaire Road
Udell      Clapham - VAN
Vanessa    Clapham Old Town
Sumaiya    Clapham Junction South
Uruguay    Paddington - VAN
Orlando    Battersea - VAN

Where the only results I wanted were:


Udell      Clapham - VAN
Vanessa    Clapham Old Town

as they match both 'clap' and 'van'

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-09 : 10:15:13

Did you try the one I posted?
you could also

WHERE [CarName] + ',' + [LocationName] Like '%'+words.[string]+'%'
and [CarName] + ',' + [LocationName] Like '%'+words.[string]+'%'

but I prefer the group by.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

taylordave
Starting Member

6 Posts

Posted - 2007-11-09 : 10:17:22

quote:
Originally posted by nr

Did you try the one I posted?

Yes, it returned no results, which I can't understand tbh as I thought that would work. I also tried different joins.

taylordave
Starting Member

6 Posts

Posted - 2007-11-09 : 10:25:51

quote:
Originally posted by nr

Did you try the one I posted?
you could also

WHERE [CarName] + ',' + [LocationName] Like '%'+words.[string]+'%'
and [CarName] + ',' + [LocationName] Like '%'+words.[string]+'%'

but I prefer the group by.

Sorry, I had made a mistake. Just tried it again with the GROUP BY and it definitely did work.

Thanks for the help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 10:32:31

NOW we are getting somewhere! Is it really that hard to remember to write all the business rules that applies to your problem?

Use the suggestion nr posted 11/09/2007 : 08:39:09

E 12°55'05.25"
N 56°04'39.16"

taylordave
Starting Member

6 Posts

Posted - 2007-11-09 : 12:45:05

quote:
Originally posted by Peso

NOW we are getting somewhere! Is it really that hard to remember to write all the business rules that applies to your problem?

In my original post

quote:

What I want to get are only those rows that match both 'clap' and 'van'.

No business rules missing. All my follow up posts did was to clarify.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources