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