INTERSECT in MSSQL 2000 on the same table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • skumar2008
    New Member
    • Jan 2008
    • 3

    #1

    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

    Code:
    BlogPostId  TagId
          1         5
          1         6
          1         7
          2         5
          2         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.
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Originally posted by skumar2008
    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

    Code:
    BlogPostId  TagId
          1         5
          1         6
          1         7
          2         5
          2         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:

    Code:
    select table1.*, table2.* from 
    (select * from mytable) table1 
    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

    Comment

    • skumar2008
      New Member
      • Jan 2008
      • 3

      #3
      CK

      Thanks for your reply.

      Shiv

      Comment

      Working...