Filter results on count (having?/where?)

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

Transact-SQL (2000)

Filter results on count (having?/where?)

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-07-24 : 05:39:55

Hi,

The following query searches my table for rows matching the words in my keywords (temp) table.

select ps.id, ps.value, ps.source, ps.colorId, k.word searchedWord
from powersearch ps
inner join @keywords k
on ps.value like '%'+k.word+'%'

If I would group them by ps.Id and count the searchedWord I get values ranging from 1 to (select count(1) from @keywords)
I only want the results that match ALL the keywords.

How would I best go about this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:20:45

[code]select ps.id,
ps.value,
ps.source,
ps.colorId,
k.word as searchedWord
from @keywords as k
inner join powersearch as ps on ps.value like '%' + k.word + '%'

declare @a int

select @a = count(*) from @keywords

set rowcount @a

select ps.id,
count(*)
from @keywords as k
inner join powersearch as ps on ps.value like '%' + k.word + '%'
group by ps.id

set rowcount 0[/code]

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

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-07-24 : 06:53:14

I think I didn't formulate my question right.
The proposed qry returns me the first x id's with their matched keyword count (where x is the number of keywords in the keywords table)

Take the next situation:

@KeyWords


id word
-- ----
1  merc
2  red

select ps.id, ps.value, ps.source, ps.colorId, k.word
from powersearch ps
inner join @keywords k
on ps.value like '%'+k.word+'%'

id          value                    src    colorId word
-------  -------------------------  -----   ------  ----
1633461  MERCEDES                   CO      157323  merc
1635581  COMMERCIAL TRANSPORT AUST  CO      508802  merc
1635583  COMMERCIAL TRANSPORT AUST  CO      308795  merc
...
1884471  KAIXUAN/VICTORY RED        CLR_NM  508802  red
1884475  RUTLAND RED                CLR_NM  508806  red

Now I only need the rows with colorId 508802 because it has at least 1 entry for both keywords.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 07:07:04

[code]DECLARE @Items INT

SELECT @Items = COUNT(*)
FROM @Keywords

SELECT x.*
FROM Powersearch AS x
INNER JOIN (
SELECT ps.colorID
FROM @Keywords AS k
INNER JOIN Powersearch AS ps ON ps.Value LIKE '%' + k.Word + '%'
GROUP BY ps.colorID
HAVING COUNT(DISTINCT k.Word) = @Items
) AS y ON y.colorID = x.colorID[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources