Selecting more than one item from one field

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)

Selecting more than one item from one field

Author

Topic

mgb71
Starting Member

3 Posts

Posted - 2004-04-05 : 10:01:36

Hi
I am new to SQL Please can someone help.

I have 3 tables,
one called organisation,
one called orgkey,
and one called keywords

It is a many to many relationship between organisations and keywords.

so tables are

orgid | orgname .....orgid | wordid.....keywordid |description
--------------- --------------- ----------------------
1.....| test .......... 1 | 1 ........... 1 | temp
2.....| test2.......... 1 | 3 ........... 2 | head
3.....| test3.......... 2 | 1 ........... 3 | nose
........................ 2 | 2 ........... 4 | eyes
........................ 3 | 1 ........... 5 | ears

What I would like to do is to show a orgname which has two keywords linked to it such as

give me the orgname which has the keywords of nose and temp

the script I wrote does not seem to pull back the data

select * from organisations o
where o.orgid in ( select distinct ok.orgid from orgkey ok,keywords k
where ok.wordid = k.keywordsid
and k.description = 'temp' AND k.description = 'nose' )

If I remove the "AND k.description = 'nose'"
it works
Can anyone help please
Thanks

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-05 : 10:08:19

You have forgotten to square the hypotenuse in your query.

Try this:

select * 
from organisations o
INNER JOIN orgkey ok
  ON ok.orgid = o.orgid
INNER JOIN keyworkds k
  ON k.keywordid = ok.wordid
WHERE k.description IN ('temp', 'nose' )

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-05 : 10:10:22

This is a homework problem isn't it ?

mgb71
Starting Member

3 Posts

Posted - 2004-04-05 : 11:49:43

thanks SamC
but I only want the orgname that has 'nose' and 'temp' which should bring just orgname of test, this brings any with a keyword of 'temp'.
I have changed the data so this is an example,
and I'm to old for homework but I'm teaching myself

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-05 : 13:07:25

I'd like to come up with a solution that involved only one recordset in the WHERE clause. In the meantime, I'll have to use the SANTA clause <sorry>

SELECT *
FROM organisation 
WHERE orgid IN (
  SELECT o.orgid
  FROM orgkey o
  INNER JOIN keywords k
     ON k.keywordid = o.wordid
  AND k.description = 'temp')
AND orgid IN (
  SELECT o.orgid
  FROM orgkey o
  INNER JOIN keywords k
     ON k.keywordid = o.wordid
  AND k.description = 'nose')

You could INNER JOIN this to death and get the same result.

SELECT o.*
FROM organisation o
INNER JOIN (
  SELECT DISTINCT o.orgid
  FROM orgkey o
  INNER JOIN keywords k ON k.keywordid = o.wordid
) X ON X.orgid = o.orgid AND k.description = 'temp'
INNER JOIN (
  SELECT DISTINCT o.orgid
  FROM orgkey o
  INNER JOIN keywords k ON k.keywordid = o.wordid
) Y ON Y.orgid = o.orgid AND k.description = 'nose'

There may be (or not) a performance benefit in the 2nd query, depends on table sizes, indexes and the stars.

mgb71
Starting Member

3 Posts

Posted - 2004-04-06 : 03:19:10

thanks SamC
This works great

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-06 : 14:56:21

Hmmm... interesting. I probably would have gone about this using a query similar to Sam's first response, but including a GROUP BY...HAVING COUNT(*) = 2 approach.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-06 : 15:37:55

Come on Mark. Show us the whole enchalada.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-06 : 16:11:33

Well, Sam, I didn't have the exact syntax worked out yet, but it would be basically the approach that NR uses in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22402&SearchTerms=having+count

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-06 : 20:46:02

[code]
SELECT *
FROM organisation og
WHERE 2 = (
SELECT COUNT (DISTINCT *)
FROM keywords k
INNER JOIN orgkey o ON o.wordid = k.keywordid
WHERE k.description IN ('nose', 'temp')
AND o.orgid = og.orgid ) [/code]

I didn't look at the execution plan, but I'd guess for large tables, this query is slower than the INNER JOIN solution posted earlier ... it looks like the set in the WHERE clause would be repeatedly executed for each og.orgid. Not a good plan.

Nigel?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-06 : 20:56:58

SamC and mgb71,

This is relational division. There a plenty of articles on the web, celko has a good one. Coupled with the CSV table approach for the IN expression, no dynamic sql is involved when the user requests n items....

DavidM

"If you are not my family or friend, then I will discriminate against you"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources