CODEWITHSUNDEEP
mysqlsql
Ramaraju.d
Ramaraju.d

Reputation: 1353

How to fetch Data from multiple tables in a single query

I have the following tables

A User table with column names (UserId,Name,email)

A Videos table with column names (UserId,VideoLink)

An Activity table with column names (UserId,Status) if status=0 likes,if status=1 disliked

I want to get the following result

Name, Total_Videos_posted_by_that_user, count(Likes), count(dislikes)

How can i Query them in a single Query?

Upvotes: 0

Views: 723

Answers (1)

juergen d
juergen d

Reputation: 204746

select u.name, 
       videocount, 
       dislikes,
       likes
from user u
left join 
(
  select u.name, count(v.videolink) as videocount
  from user u
  inner join videos v on v.userid = u.userid
) x on x.name = u.name
left join 
(
  select u.name, 
         sum(a.status=1) as dislikes,
         sum(a.status=0) as likes
  from user u
  inner join activity a on a.userid = u.userid
) y on y.name = u.name

SQLFiddle demo

Upvotes: 3

Related Questions