1

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

5
  • Create a dbfiddle.uk/?rdbms=mysql_8.0 or similar with the tables and sample data
    – Lennart
    Apr 3 '20 at 5:03
  • @Lennart Added a dbfiddle with the tables and sample data in this post.
    – mike16889
    Apr 3 '20 at 5:28
  • Not sure I understand the question, but you may consider splitting your query in 2 cases and the combine them with UNION
    – Lennart
    Apr 3 '20 at 5:52
  • @Lennart Basically If a user's usergroup has no itemTypes assigned, it should fall back to the clients usergroups, but if it does have any assigned, it should only show the ones for the usergroup (as long as they are also in the clients itemTypes, so if an item type is removed from a client, no user can see it.)
    – mike16889
    Apr 3 '20 at 6:04
  • 1
    These are the two cases I'm refering to. See if you can get case 1 right, then 2 and combine the two queries with union. I will see if i get time later today, but I think you should try this approach
    – Lennart
    Apr 3 '20 at 6:10
2

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

5
  • Some suggestions. Use EXISTS and NOT EXISTS instead of the join's against the derived tables. Then you don't have to join in users once again, instead reference users from the outer select
    – Lennart
    Apr 3 '20 at 7:16
  • See dbfiddle.uk/… for an example
    – Lennart
    Apr 3 '20 at 7:33
  • @lennart that helps a lot. Had no idea 'exists' and 'not exists' existed (self taught, still learning) if you wanted to make that into an answer I will accept it.
    – mike16889
    Apr 3 '20 at 8:29
  • Add it to your answer as an alternative solution. You can accept that in 2 days I think
    – Lennart
    Apr 3 '20 at 8:37
  • @lennnart Will do later. Thanks again.
    – mike16889
    Apr 3 '20 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.