CODEWITHSUNDEEP
mysqlsqlunion
aytek
aytek

Reputation: 1942

SQL INNER JOIN and COUNT

I have 3 tables (user, item, userlike) and 2 sql queries. How can I unify these two queries?

SELECT item.userid, item.id, user.name FROM item 
INNER JOIN user ON item.userid = user.id 

SELECT userid,itemid, COUNT(*) AS `liked` FROM userlike
WHERE userid=9
GROUP BY itemid

I want to know whether a specific user (9) has liked the item or not.

Result should be somthing like this

itemid userid name liked* (*whether 'user 9' liked this item or not)
1      7      foo  0
2      4      asd  1

Thanks

Upvotes: 1

Views: 1703

Answers (3)

peterm
peterm

Reputation: 92785

You want to use an OUTER JOIN for this

SELECT i.id itemid, u.id userid, u.name, COALESCE(liked, 0) liked
  FROM item i JOIN user u
    ON i.userid = u.id LEFT  JOIN 
(
  SELECT itemid, COUNT(*) liked 
    FROM userlike 
   WHERE userid = 9
   GROUP BY itemid
) l
    ON i.id = l.itemid;

or

SELECT i.id itemid, u.id userid, u.name, l.userid IS NOT NULL liked
  FROM item i JOIN user u
    ON i.userid = u.id LEFT  JOIN userlike l
    ON i.id = l.itemid
   AND l.userid = 9;

Sample output:

| ITEMID | USERID |  NAME | LIKED |
|--------|--------|-------|-------|
|      2 |      4 | user4 |     1 |
|      1 |      7 | user7 |     0 |

Here is SQLFiddle demo

Upvotes: 2

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

You don't need to use COUNT. You just need to know if there is an entry for a specific item and a specific user in the userlike table :

SELECT i.id as itemid, u.id as userid, u.name,
       case when ul.userid is null then 0 else 1 end as liked
FROM item i
INNER JOIN user u ON i.userid = u.id
LEFT OUTER JOIN userlike ul ON i.id = ul.itemid AND ul.userid=9
ORDER BY i.id

Upvotes: 0

DevlshOne
DevlshOne

Reputation: 8457

SELECT item.id, item.userid, user.name, userlike.liked
FROM item
JOIN user ON user.id = item.userid
JOIN userlike ON item.id = userlike.itemid
WHERE userlike.liked = 1
GROUP BY item.id

OR

SELECT item.id, item.userid, user.name, userlike.liked
FROM item
JOIN user ON user.id = item.userid
JOIN userlike ON item.id = userlike.itemid
WHERE COUNT(userlike.liked) >= 1
GROUP BY item.id

Upvotes: 0

Related Questions