How to query this in SQL Server?

Question

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 List<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.

How are you passing the list of tags in? Or is that the gist of the question really (In which case see [here](http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause))? — Martin Smith, Sep 06 '11 at 11:23
I have a list of tag object and a list of extracted tag string. — DucDigital, Sep 06 '11 at 11:28

score 1 · Accepted Answer · answered Sep 06 '11 at 11:42

1

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

answered Sep 06 '11 at 11:42

nikc.org

16,462
6
50
83

score 1 · Answer 2 · answered Sep 06 '11 at 16:26

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.

Sachin Shanbhag · Answer 3 · 2011-09-06T11:42:44.707

0

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.

edited Sep 06 '11 at 11:42

answered Sep 06 '11 at 11:37

Sachin Shanbhag

54,530
11
89
103

score 0 · Answer 4 · answered Sep 06 '11 at 11:37

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.)

How to query this in SQL Server?

4 Answers4