Select rows with specific multiple values from the same column?

Question

I have database where 2 roles can't be associated with each other, and I need to display any users who have conflicting roles.

For example: an (id 2) accountant can't also be a (id 5) trainer

this has to be done without using CTE's

     Table a                    Table b                  table c
---------------            -------------------        ------------
userID | roleID            roleID | conflictID           roleID | Role Name

  1        2                 2           5                  1      chef
  1        3                                                2      accountant
  1        5                                                3      driver
  2        3                                                4      barmaid
  2        1                                                5      trainer
  3        2
  3        3

the result should contain only the userID who has both roles 2 and 5

userID
------
  1

What is a 'CTE'? You don't show what you tried or the structure of the tables. — Jay
– Jay, Commented Sep 6, 2018 at 14:39

jarlh · Accepted Answer · 2018-09-06 14:51:33Z

1

Join the b table with the a table twice, to get userID's with conflicting combinations:

select distinct a1.userid
from tableb b
join tablea a1 on b.roleID = a1.roleID
join tablea a2 on b.conflictID = a2.roleID
              and a1.userID = a2.userID

edited Sep 6, 2018 at 14:51

answered Sep 6, 2018 at 14:43

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select rows with specific multiple values from the same column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related