CODEWITHSUNDEEP
sqlsql-serverdatatables
Harry T
Harry T

Reputation: 131

Where condition based on link table

I have a table of Users. Each User can be in multiple Disciplines, and they are linked by a link table, User_Discipline. The tables are pretty straight forward:

User
ID     Name          more...
3   |  John Doe    | ...
7   |  Jane Smith  | ...
12  |  Joe Jones   | ...

Discipline 
ID     name
1   |  Civil
2   |  Mechanical
3   |  Piping

User_Discipline
UserID       DisciplineID
3        |   2
3        |   1
7        |   2
12       |   3

Say John Doe is the logged in user. He needs to be able to select a list of all of the users in any of his disciplines. In the given example, I need a query that would return a list with John and Jane, since John is both Civil and Mechanical, and Jane is in Mechanical.

I think sub-selects are involved, but all the reading I've done so far have shown how to do subselects checking for one value (say, John's Civil Discipline). I need to be able to perform a query that runs a WHERE condition but matches any of John's Disciplines many-to-many with others' Disciplines.

I'm using the DataTables Editor .NET library to do the SQL, but I can translate an answer in regular SQL markup to that library. The only limitation of the library that I might encounter here is that everything would have to be done in one SQL statement. I appreciate any help!

Upvotes: 0

Views: 392

Answers (2)

DVT
DVT

Reputation: 3127

Something like this?

SELECT DISTINCT [User].ID, [User].Name
FROM [User]
  JOIN User_Discipline
    ON [User].ID = User_Discipline.UserID
WHERE 
  User_Discipline.DisciplineID IN (
    SELECT DisciplineID
    FROM User_Discipline
    WHERE UserID = <<John Doe's userID>>
  )

Upvotes: 2

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

You can do it all with inner joins:

declare @users table (id int, fullname varchar(50)) 
declare @disciplines table (id int, discname varchar(50))
declare @userdisciplines table (userid int, discid int)

insert into @users VALUES (3, 'John Doe') 
insert into @users VALUES (7, 'Jane Smith')
insert into @users VALUES (12, 'Joe Jones')

insert into @disciplines VALUES (1, 'Civil') 
insert into @disciplines VALUES (2, 'Mechanical') 
insert into @disciplines VALUES (2, 'Piping') 

insert into @userdisciplines VALUES (3, 2)
insert into @userdisciplines VALUES (3, 1)
insert into @userdisciplines VALUES (7, 2) 
insert into @userdisciplines VALUES (12, 3)

SELECT distinct id, fullname from @users u 
INNER JOIN @userdisciplines ud ON ud.userid = u.id
INNER JOIN
(SELECT ud.discid FROM @users u 
inner join @userdisciplines ud on ud.userid = u.id 
WHERE u.fullname = 'John Doe') d ON d.discid = ud.discid

Upvotes: 1

Related Questions