Check All

• Jaya Chitra

  SSCommitted

  Points: 1916

  More actions

  July 22, 2008 at 1:41 am

  #189971

  Dear Friends,

  I have a table which contains the rights of groups for the file types.

  Another table containing the file types.

  Now I have to extract the groups who has the rights to all the file types or the selected file types.

  For e.g:

  GroupFileTypes

  GroupID FileTypeID

  --------------------

  1 2

  1 3

  1 4

  2 1

  2 2

  2 3

  3 3

  3 4

  4 2

  4 3

  The query has to return the group 1,2,4 if I am giving the file type 2,3.

  Can any one help me in this regard?

  Thanks a lot in advance.

  [font="Arial"]Nothing is impossible with Hard Work[/font]:)

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 22, 2008 at 1:53 am

  #846202

  Assume that the selected file types are in a table such as this

  CREATE TABLE SelectFileTypes(FileTypeID INT)

  INSERT INTO SelectFileTypes(FileTypeID) VALUES(2)

  INSERT INTO SelectFileTypes(FileTypeID) VALUES(3)

  This query should give the results you want

  SELECT GroupID

  FROM GroupFileTypes

  WHERE FileTypeID IN (SELECT FileTypeID FROM SelectFileTypes)

  GROUP BY GroupID

  HAVING COUNT(DISTINCT FileTypeID)=(SELECT COUNT(*) FROM SelectFileTypes)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Jaya Chitra

  SSCommitted

  Points: 1916

  More actions

  July 22, 2008 at 3:14 am

  #846239

  Dear Mark,

  Thank you a lot it is working fine.

  🙂

  [font="Arial"]Nothing is impossible with Hard Work[/font]:)