Is there a way to do this in SQL

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

SQL Server Development (2000)

Is there a way to do this in SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-31 : 08:26:01

Chris writes "I am newbie to sql and I am attempting to do the following am not sure if its possible or not:
I have a Table named TopicRelations. In that table there are two fields TopicID and SubjectID. I want to be able to SELECT all TopicIDs that have SubjectID say '0001' AND '0002' AND '0003'

Example:


TopicID   |    SubjectID
------------------------
0001      |         0001
------------------------
0001      |         0002
------------------------
0001      |         0003
------------------------
0001      |         0017
------------------------
0002      |         0001
------------------------
0002      |         0002
------------------------
0003      |         0003
------------------------
0003      |         0017

The example table above would only return topicid 0001 because that is the only topic that has topicid 0001 AND 0002 AND 0003.
Is there someway a query of this sort would be possible in sql. If not do you have any suggestions on what would be the best way to accomplish this in code. Thanks!!"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-31 : 08:43:32

There are several ways - here's one

select TopicID
from tbl
where SubjectID in ('0001','0002','0003')
group by TopicID
having count (*) = 3

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/31/2002 08:44:20

Nazim
A custom title

1408 Posts

Posted - 2002-01-31 : 08:46:47

select topicid
from table
where subjectid in('0001','0002',0003')
group by topicid
having count(distinct subjectid)=3

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 08:49:21

Hey Nazim, stop plagiarizing!

Nazim
A custom title

1408 Posts

Posted - 2002-01-31 : 08:54:33

Nooooooooo! i didnt copied for once

its just that i had opened multiple posts and when i posted on this one nr had already did it

.

btw, cant you see the difference in our querys he uses Propercase i dont and i use table and he uses tbl

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 01/31/2002 09:04:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources