SQL query with two linked tables

Question

Please help to write this query. This is the table diagram:

User responds for selling drugs for specific sales types. How to find users that responds for same drugs in same sales type for specific user id?

For example:

• Alice respond for selling vitaminA, vitaminB in retail
• Bob respond for selling vitaminA, vitaminB in retail and state market
• Charlie respond for VitaminB, VitaminC in state market

Users that responds same salestype-drugs

• for Alice is Bob
• for Bob is Alice and Charlie
• for Charlie is Bob

Answer 1

For Given Example:

For the example data where users sell the same drugs through all sales types:

SELECT mus.userId 
FROM User2SalesType qus 
INNER JOIN User2Drug qud       ON qus.UserId = qud.UserId
INNER JOIN User2SalesType mus  ON qus.SalesTypeId = mus.SalesTypeId
                               AND mus.UserId != qus.UserId
INNER JOIN User2Drug mud       ON qud.DrugId = mud.DrugId 
                               AND mus.UserId = mud.UserId
WHERE qus.UserId = ? # querying user ID

---

For More Flexible Data:

If you want to support a user selling drug A by sales X and drug B by sales Y (but not drug A by sales Y or drug B by sales X) you can use this data model:

User:       id, etc.
Response:   userId, salesTypeId, drugId
SalesType:  id, etc.
Drug:       id, etc.

Then you can query by self-joining Response:

SELECT mu.userId 
FROM response qu                       # querying user
INNER JOIN response mu                 # matching users
  ON qu.salesTypeId = mu.salesTypeId
  AND qu.drugId = mu.drugId
  AND qu.userId != mu.userId
WHERE qu.userId = ?                    # ? = querying user ID