CODEWITHSUNDEEP
mysql
PWL
PWL

Reputation: 466

MySQL: Select whether or not user is in another table

Keep in mind there's more to both the statement and the tables, but I'll keep it short for easier reading.

I'm trying to get whether or not a user has been in a shop or not. The tables look like this. 

--Shop--
ShopID

--Visit--
ShopID
UserID

And the following is how far I've come at the moment.

SELECT shop.ShopID, visit.UserID AS Visited FROM shop
LEFT OUTER JOIN visit ON shop.ShopID = visit.ShopID
WHERE UserID = 1

The problem with this code is that it will not show NULL for visits that's not UserID 1. I've tried to add OR UserID IS NULL to the statement, but it just leaves me with all the shops that has not been visited by any user.

I could filter it by checking whether Visited is the same as the UserID or not, but it would be a lot easier if the database could write it as 1 and 0. So to ask specific:

How do I make Visited output 0 or 1 based on if there's a UserID 1 connected with the ShopID in the Visit-table?

Upvotes: 1

Views: 114

Answers (4)

seahawk
seahawk

Reputation: 1912

SELECT shop.ShopID, if(visit.UserID is null,0,1) AS Visited FROM shop
LEFT OUTER JOIN visit ON (shop.ShopID = visit.ShopID
AND visit.UserID = 1 )

Upvotes: 0

Paulo Avelar
Paulo Avelar

Reputation: 2140

Dennis Klopfer's answer is the way to go for simplicity and performance. But maybe you want something more like that, to check if a given user ever visited each shop:

shop |  visited by user 1
 #1  |       false
 #2  |       true

If that's the case, I'd try something like this:

   SELECT s.ShopID, IF(ISNULL(v.UserID), FALSE, TRUE) AS visited
     FROM shop S LEFT JOIN visit v ON s.ShopID = v.ShopID AND v.UserID = 1

Of course, you can also add a WHERE clause to your query and filter the shop list by any field.

Upvotes: 1

Dennis Klopfer
Dennis Klopfer

Reputation: 769

The problem with your join is that you keep all the 'shopID's in the list, as you just create a list which expands all the shops and visits with the shops that have not been visited. If I get you right what you want is the shops visited by user = 1. This can be achieved very easily by just using the Visit table.

Select DISTINCT visit.shopID FROM visit
WHERE UserID = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You are trying to accomplish this, I think:

SELECT s.ShopID, v.UserID AS Visited
FROM shop s LEFT OUTER JOIN
     visit v
     ON s.ShopID = v.ShopID AND v.UserID = 1;

You need to put the condition on v in the ON clause rather than the WHERE clause.

However, if a user can visit multiple times, then you'll get multiple rows. So, I would recommend using a subquery in the SELECT to get an arbitrary row with the user:

select s.ShopId,
       (select v.UserId
        from visit v
        where s.SHopId = v.ShopId and v.userId = 1
        limit 1
       ) as UserId
from shop s;

Upvotes: 0

Related Questions