CODEWITHSUNDEEP
mysql
iamtravisw
iamtravisw

Reputation: 341

Need All MySQL Results with value UserID or NULL - No additional UserIDs

When a user is on my website, and they click 'Add' or 'Remove' item... The Add or Remove message is the opposite of what it should be SOMETIMES. For example, when you click a button, it will change colors. It will be gray is value is NOT 'Y' and red if it is 'Y'. Sometimes it is reversed. This is because of the query below GROUP BY clause which I am using to avoid duplicates (which happens in the collection table, because each user adds a new record to this Intersection table). Without the group by clause, there are many duplicates. 

SELECT a.AmiiboID, a.Name, a.ImageURL, c.Favorited, c.Collected, c.WishList, c.UserID
FROM Amiibo a
LEFT JOIN Collection c ON a.AmiiboID = c.AmiiboID
GROUP BY a.AmiiboID

Results from above query:

I have also tried doing this:

SELECT a.AmiiboID, a.Name, a.ImageURL, c.Favorited, c.Collected, c.WishList, c.UserID
FROM Amiibo a
LEFT JOIN Collection c ON a.AmiiboID = c.AmiiboID
WHERE c.UserID = 2 OR c.UserID IS NULL

Results from above query... Notice AmiiboID 0-4 are missing because they were added by UserID1

Results from above query... Notice AmiiboID 0-4 are missing because they were added by UserID1

However the problem with this is that it excludes records that were added by another user. So if row 1-5 were entered by userid 1... it would not show row 1-5.

I need to always get ALL records from the Amiibo table AND records from the Collection that have UserID 2 (in this case). And if another user (such as UserID: 1) has added a records, ignore them. I also need there to be no duplicates.

Is this possible?

Thank you, Travis W.

Upvotes: 0

Views: 33

Answers (2)

Barmar
Barmar

Reputation: 780851

Put the c.UserID check in the ON clause when using LEFT JOIN.

SELECT a.AmiiboID, a.Name, a.ImageURL, c.Favorited, c.Collected, c.WishList, c.UserID
FROM Amiibo a
LEFT JOIN Collection c 
    ON a.AmiiboID = c.AmiiboID
        AND c.UserID = 2

There's no need to check for NULL in this case.

Upvotes: 1

Victor
Victor

Reputation: 3978

Please tell me if something like this works for you...

-- Gets all the rows from amiibo "expanded" with the collection
SELECT a.AmiiboID, a.Name, a.ImageURL, c.Favorited, c.Collected, c.WishList, c.UserID
FROM Amiibo a
LEFT JOIN Collection c ON a.AmiiboID = c.AmiiboID

UNION DISTINCT

-- Gets all the rows from collection "expanded" with amiibo that belongs to userId 2 or null
SELECT a.AmiiboID, a.Name, a.ImageURL, c.Favorited, c.Collected, c.WishList, c.UserID
FROM Collection c
LEFT JOIN Amiibo a ON a.AmiiboID = c.AmiiboID
WHERE c.UserID = 2 OR c.UserID IS NULL

Upvotes: 0

Related Questions