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.
| Author |
Topic |
|
natg504
Starting Member
14 Posts |
 Posted - 2008-08-14 : 09:19:29
|
I have two tables, document & document_topic. The structure is something like this below:document - document_id (PK)
- title
- date
- etc..
document_topic The document_topic table can have multiple records for each document_id. So, if a document was associated with several topics, I can grab all of those. What I want to do is write a query that will grab only documents associated with 2 specific topics. For example, grab all documents that have topic #10 AND topic #11 checked. What is the best way to do this? Is it possible with just one query?Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-14 : 09:21:58
|
[code]select *from document d inner join document_topic t on d.document_id = t.document_idwhere t.topic_id in (10, 11)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
natg504
Starting Member
14 Posts |
Posted - 2008-08-14 : 09:34:34
|
| Thanks, but that query would not quite work because if I have a document that has only topic #10 & not #11, it would still get pulled. I want to pull it only if it has both #10 & #11. |
|
|
|
natg504
Starting Member
14 Posts |
Posted - 2008-08-14 : 09:42:53
|
| I guess I could do something like this, but I don't know if this is the best way:SELECT DISTINCT a.document_id, Count(a.document_id) AS ctFROM document AS a INNER JOIN document_topic AS b ON a.document_id = b.document_idWHERE b.topic_id In (10,11)GROUP BY a.document_idHAVING Count(a.document_id)=2; |
|
|
|
|
|
|
What is the best way to write this query?