CODEWITHSUNDEEP
mysqlsql
amir-f
amir-f

Reputation: 731

Find the frequency of rows from multiple joint tables

I have this problem with SQL and I can't figure it out. Imagine that I have 3 tables as follows

   Names
   Nameid  name
   1       Starbucks Coffee
   2       Johns Restaurant
   3       Davids Restaurant

   user_likes
   userid   Nameid
   1        1
   2        1
   2        3

   user_visited
   userid   Nameid
   1        2

I want to find the places with the most number of (likes+visited). I also want to select all places not just those who have been liked or visited

I do:

 SELECT n.nameid, n.name , COUNT(f.nameid) AS freq
 FROM names AS n
 LEFT JOIN user_likes ON n.nameid=user_likes.nameid
 LEFT JOIN user_visited ON n.nameid=user_visited.nameid
 ORDER BY freq DESC

But it doesn't give me the total frequency. The problem is, if a place is both visited and liked, it is counted only once, while I want it to be counted twice. Any suggestions?

Upvotes: 0

Views: 884

Answers (3)

amir-f
amir-f

Reputation: 731

Mosty, your usage of coalesce() gave me an idea and I came up with this:

  SELECT n.nameid, n.name , 
  SUM((IFNULL(user_likes.userid,0)>0)+(IFNULL(user_visited.userid,0)>0) ) AS freq
  FROM names AS n LEFT JOIN user_likes ON n.nameid=user_likes.nameid LEFT JOIN 
  user_visited ON n.nameid=user_visited.nameid ORDER BY freq DESC

Since my example here was a simplification of my problem (I have to join more than two tables to the main table) I'm reluctant to use SELECT inside SELECT, because I know it's not very efficient. Do you see any fundamental problem with my solution?

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

I've made a quick test and although I prefer Serge's solution, this one seemed to perform faster as the amount of items to join will be less:

SELECT n.nameId, n.name, coalesce(sum(likesCount), 0) totalCount FROM NAMES n
LEFT JOIN (
  SELECT nameId, count(*) likesCount FROM user_likes
  GROUP BY nameId
  UNION ALL
  SELECT nameId, count(*) visitsCount FROM user_visited
  GROUP BY nameId
) s ON n.nameId = s.nameId
GROUP BY n.nameId
ORDER BY totalCount DESC

I'm assuming the following indexes:

alter table names add index(nameid);
alter table user_likes add index(nameid);
alter table user_visited add index(nameid);

Probably the OP can compare the efficiency of both queries with actual data and provide feedback.

Upvotes: 2

Serge Seredenko
Serge Seredenko

Reputation: 3541

SELECT n.name, t.nameid, COUNT(t.nameid) AS freq
FROM Names n
JOIN (
    SELECT nameid FROM user_likes
    UNION ALL
    SELECT nameid FROM user_visited
) t
ON n.nameid = t.nameid
GROUP BY t.nameid ORDER BY freq DESC

Upvotes: 2

Related Questions