IN operator - 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

Transact-SQL (2005)

IN operator

Author

Topic

jameswoodmancy
Starting Member

13 Posts

Posted - 2009-08-25 : 10:54:46

Hi,

I have a table

key ID TopicID NAME
1 1 4 Science
2 1 7 History

I want to do something like this but on a AND basis

SELECT DISTINCT ID FROM [TABLENAME]
WHERE TopicId In (4,7)

for example if i do

SELECT ID FROM [TABLENAME]
WHERE TopicId In (4, 7, 9)

it will still bring back ID 1 because its included in the IN statement. How do i do this so i only ANDS so it will only return ID if it mathces all 4, 7, 9. Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-25 : 10:58:12

[code]SELECT ID
FROM TableName AS t
INNER JOIN (
SELECT 4 AS k UNION ALL
SELECT 7
) AS x ON x.k = t.TopicID
GROUP BY ID
HAVING COUNT(*) = 2

SELECT ID
FROM TableName AS t
INNER JOIN (
SELECT 4 AS k UNION ALL
SELECT 7 UNION ALL
SELECT 9
) AS x ON x.k = t.TopicID
GROUP BY ID
HAVING COUNT(*) = 3[/code]

N 56°04'39.26"
E 12°55'05.63"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 10:59:16

How does a single record have more than one TopicID?

An infinite universe is the ultimate cartesian product.

jameswoodmancy
Starting Member

13 Posts

Posted - 2009-08-25 : 12:16:59

its a one to many relationship...

thanks peso - i like that. and it works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources