469,635 Members | 2,390 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,635 developers. It's quick & easy.

INTERSECT in MSSQL 2000 on the same table

I've seen a number of solution for INTERSECT in MSSQL 2000 but all the examples I've seen go across two tables.

What I need is to be able to INTERSECT records with multiple criteria from the same table.

For example:
Lsts say I have a BlogPostTag table where I store the BlogPostId and the TagId that pertains to this blog post. Further, a BlogPost can be associated to multiple tags. So I could have a BlogPost associated to the tags C# and ASP.NET.

sample data

Expand|Select|Wrap|Line Numbers
  1. BlogPostId  TagId
  2.       1         5
  3.       1         6
  4.       1         7
  5.       2         5
  6.       2         7
  7.       2         8
I now need a query that will give me the BlogPostIds of all posts that have the TagIds 5 AND 7 (result should be BlogPostIds 1 AND 2)

Give me the BlogPostIds of all posts that have the TagIds 5 AND 6 AND 7. The result should be BlogPostId 1 only.

I hope that makes sense.
Jan 18 '08 #1
2 2462
ck9663
2,878 Expert 2GB
I've seen a number of solution for INTERSECT in MSSQL 2000 but all the examples I've seen go across two tables.

What I need is to be able to INTERSECT records with multiple criteria from the same table.

For example:
Lsts say I have a BlogPostTag table where I store the BlogPostId and the TagId that pertains to this blog post. Further, a BlogPost can be associated to multiple tags. So I could have a BlogPost associated to the tags C# and ASP.NET.

sample data

Expand|Select|Wrap|Line Numbers
  1. BlogPostId  TagId
  2.       1         5
  3.       1         6
  4.       1         7
  5.       2         5
  6.       2         7
  7.       2         8
I now need a query that will give me the BlogPostIds of all posts that have the TagIds 5 AND 7 (result should be BlogPostIds 1 AND 2)

Give me the BlogPostIds of all posts that have the TagIds 5 AND 6 AND 7. The result should be BlogPostId 1 only.

I hope that makes sense.
i have not had a chance to figure this out yet. but if you need an immediate answer to this one i would suggest to use the example that you mentioned. even if it's coming from two different table you can still use it by doing a subquery.

this:

Expand|Select|Wrap|Line Numbers
  1. select table1.*, table2.* from 
  2. (select * from mytable) table1 
  3. left join (select * from mytable) table2 on table1.key = table2.key
is an example of how a single table can be accessed twice as if they are two different tables. try using the same technique in the sample that you saw...

--- CK
Jan 18 '08 #2
CK

Thanks for your reply.

Shiv
Jan 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by David | last post: by
4 posts views Thread by AJA | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.