How to query this in SQL Server?

I have the ff schema:

Faculty

FacultyID             
Name                  

Tags

TagID
TagValue

tags_faculty

TagID
FacultyID

The question is how to find out the faculty that have the any TagValue that provided in the 开发者_StackOverflowList<tags>. (please note that due the design of some DBA, the tagvalue was not unique, user has the same tagvalue, but the tagid is different)

I am using SQL Server and Linq in C#. But I am open to queries, procedure, linq expression as long as the result is correct.

---

I would do that as an INNER JOIN.

SELECT f.FacultyID, f.Name
FROM faculty f
INNER JOIN tags_faculty tf ON tf.FacultyID = f.FacultyID
INNER JOIN tags t ON t.tagID = tf.tagID
WHERE t.tagID IN (<list of tagID:s>) 
GROUP BY f.FacultyID

---

Try the following with LINQ to SQL where TagsToMatch is your in memory list of strings.

List<int> TagsToMatch = tags.Select(tag => TagID);
var matchingFaculty =
     from facultyTag in dc.FacultyTags
     where TagsToMatch.Contains(facultyTag.TagID)
     select facultyTag.Faculty;

If you're using EF 4, it should optomize out your M-M relationship so you could change it to the following. EF 1 did not support the Contains syntax.

var matchingFaculty = 
     from tag in dc.Faculties
     where !TagsToMatch.Contains(tag.TagID)
     from faculty in tag.Faculties
     select faculty;

Either way, you may want to use a Distinct clause at the end in cases where faculty members are in multiple tags that match.

---

SELECT
  *
FROM
  faculty
WHERE
  EXISTS (
    SELECT
      *
    FROM
      tags_faculty
    WHERE
      TagID IN (SELECT TagID FROM tags WHERE TagValue IN (<your list of tag values>))
      AND FacultyID = faculty.FacultyID
  )

There are many options on how to achieve the list of tag values.

You may just generate as the query as a string, and shove the tags in yourself. But you need to be careful of injection attacks, etc.

Or you may generate a single string with all your tags separated by , and then use a function to split that out into a table of values. (There are Many such functions available on SO and other sources.)

---

If I understood your question correctly, then I think this query should help you. May be a bit inefficient query, but check if this gives the result you are looking for -

select FacultyID, Name
from faculty 
where facultyID in
( select tf.FacultyID from tags_faculty tf
  inner join tags t
  on t.tagID = tf.tagID 
  where t.tagValue in ( <your tag value list> )
)

Maybe an additional DISTINCT clause might help too.