Conditional inner join

Question

I need to get allowed itemTypes for a user based on 2 different tables in a conditional way. If the users userGroup has any entries in the userGroupItemTypes table it should only show items that are in common between userGroupItemTypes AND clientItemTypes but if no entries exist in userGroupItemTypes for that users userGroup, then it should just get all the ones from clientItemTypes

itemTypes

itID    itemType
1       Item 1
2       Item 2
3       Item 3

clients

cID     client
1       Client 1

userGroups

ugID    userGroup
1       Group 1
2       Group 2

users

uID     user     cID     ugID
1       Fred     1       1
2       Sam      1       2

clientItemTypes

cID     itID
1       1
1       2
1       3

userGroupItemTypes

ugID    itID
2       1
2       2

Resaults should be:

uID     itID     user     itemType
1       1        Fred     Item 1
1       2        Fred     Item 2
2       1        Sam      Item 1
2       2        Sam      Item 2
2       3        Sam      Item 3

Basically i think the query should function like follows:

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
IF((SELECT COUNT(*) FROM userGroupItemTypes GROUP BY uID) > 0, INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID, DO NOTHING)

But I do not know how to actually achieve this. Any help would be greatly appreciated

EDIT: Here is a dbfiddle

Answer 1

Based on Lennarts suggestion I have used a UNION to get the results I need:

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total > 0
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total = 0
GROUP BY uID, itID

dbfiddle

Basically running the same queries twice, one requiring a userGroup to have itemTypes, the other requiring the userGroup NOT to have itemTypes, then using the union to combine the results.

I'm not sure if this is the most efficient way of doing it but it will have to do unless I can find another way of doing it.

---

Alternative Solution (thanks to Lennart):

Using EXISTS and NOT EXISTS

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
WHERE EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
WHERE NOT EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID

dbfiddle