CODEWITHSUNDEEP
mysql
Niladri Banerjee - Uttarpara
Niladri Banerjee - Uttarpara

Reputation: 2329

MySQL left join shows duplicate rows

I have two MySQL tables - 1) users and 2) warehouseMapping.

users

id, name

warehouseMapping

id, userId, warehouseId

From the above you can see that "warehouseMapping" table is the mapping table which will show that which warehouseIds are related to a particular user. There will be multiple warehouseIds for an user. 

SELECT * FROM `users`
LEFT JOIN warehouseMapping
    ON warehouseMapping.userId = users.id
WHERE 1 AND warehouseMapping.warehouseId IN (1, 2)

My intention is to show the users who have the warehouse id 1 and 2.

After submitting the same, MySQL showing me the same user name twice.

Inputs for users table:

id                  name
***************************
1                   Niladri
2                   Tanay

Inputs for warehouseMapping table:

id   userId        warehouseId
*****************************
1      1           1
2      1           2
3      1           3
4      2           1
5      2           2

Upvotes: 0

Views: 39

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

If you want to find users who have both warehouses, here is one way:

SELECT u.id, u.name
FROM users u
LEFT JOIN warehouseMapping w
    ON w.userId = u.id
WHERE w.warehouseId IN (1, 2)
GROUP BY u.id
HAVING COUNT(DISTINCT w.warehouseId) = 2;

Upvotes: 3

Related Questions