keywords - SQL Server Forums

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

.NET Inside SQL Server (2005)

keywords

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-05-16 : 15:45:11

How can I find distinct ID'd in this table that has similar keywords to an ID I pass in like for example I pass in 1. It has 3 keywords. I would bring back 14 and 4 because 14 has bike and 4 has VAN

ID Keyword
1 car
1 boat
1 van
14 test
14 car
14 bike
4 VAN
5 scooter

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 15:47:54

I don't understand your output nor your explanation.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-05-16 : 15:55:42

ID has 3 keywords in it. I am trying to find other id's that have similar keywords. Like ID 14 also has car and ID 4 has VAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 16:02:39

In your original post, it says 14 would appear because it has bike. So that was very confusing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-16 : 16:14:44

Here's one way:


declare @t table (ID int, Keyword varchar(10))
insert @t
select 1, 'car' union all
select 1, 'boat' union all
select 1, 'van' union all
select 14, 'test' union all
select 14, 'car' union all
select 14, 'bike' union all
select 4, 'VAN' union all
select 5, 'scooter'


declare @id int
set @id = 1

select id
from   @t
where  keyword in
       (
       select keyword
       from   @t
       where  id = @id
       )
and id <> @id

output:
id
-----------
14
4

Be One with the Optimizer
TG

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-05-19 : 09:47:09

But I would need to do multiple id's like 1 and 14 and....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-19 : 14:05:13

quote:
Originally posted by helixpoint

But I would need to do multiple id's like 1 and 14 and....

That wasn't your original request. Try to be more specific next time.:

quote:
...that has similar keywords to an ID I pass in...

You can change the input to varchar instead of INT, then use a parsing table-valued-function to JOIN:
ie:
This code assumes you have a UDF called [fnParseCSV] which returns an int column called [outputint]. search for "parsing routines" or "CSV" for examples


declare @ids varchar(max)
set @ids = '1,14'

select t.id
from   @t t
left join dbo.fnParseCSV(@IDs) x
              on x.outputint = t.id
where  keyword in
       (
       select keyword
       from   dbo.fnParseCSV(@IDs) f
       join   @t t on t.id = f.outputint
       group by keyword
       )
and x.outputint is null

Be One with the Optimizer
TG

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-05-19 : 14:44:29

I can't find fnParseCSV anywhere???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-19 : 15:06:38

quote:
Originally posted by helixpoint

I can't find fnParseCSV anywhere???

Ugh...I made up the name...you have to use one you already have. If you don't have one and don't know how to create one then do the search I suggested to learn about them. You can even simply use one that has been posted.

EDIT:
here ya go - all you ever wanted to know about splitting a CSV
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources