Hi All,
I have 4 tables namely User_profile , Friends, Video , Audio.
Friend, Image , Video and Audio table datas are related to Users. All these tables holds Foreignkey to User_profile table. I want to generate reports for user usage details.
When I tried to run simple independent SQL I am able to get the proper count for each sql output.
SQL to generate User -- > friend count for each user
select up.user_id
count (f.owner_user_id) as friendcount
from user_profile up
LEFT OUTER JOIN friend f ON (up.user_id = f.owner_user_id )
group by up.user_id order by user_id;
SQL to generate User -- > video count for each user
select up.user_id
count (i.owner_user_id) as imagecount
from user_profile up
LEFT OUTER JOIN image i ON (up.user_id = i.owner_user_id )
group by up.user_id order by user_id;
SQL to generate User -- > video count for each user
select up.user_id
count (v.owner_user_id) as videocount
from user_profile up
LEFT OUTER JOIN video v ON (up.user_id = v.owner_user_id )
group by up.user_id order by user_id;
SQL to generate User -- > Audio count for each user
select up.user_id
count (a.owner_user_id) as videocount
from user_profile up
LEFT OUTER JOIN audio a ON (up.user_id = a.owner_user_id )
group by up.user_id order by user_id;
I want to combine all these SQL into single Query and want to generate singe output. But output data was wrong. The Counts are multiplied.
select up.user_id
, count (f.owner_user_id) as friendcount
,count (i.owner_user_id) as imagecount
, count(v.user_id) as videocount
,count(a.user_id) as audiocount
from user_profile up
LEFT OUTER JOIN friend f ON (up.user_id = f.owner_user_id )
LEFT OUTER JOIN image i ON (up.user_id = i.owner_user_id )
LEFT OUTER JOIN video s ON (up.user_id = v.user_id )
LEFT OUTER JOIN audio a on (up.user_id = a.user_id)
group by up.user_id order by user_id;
I don't know what am I missing in the above SQL. It would be great help if someone can help me in fixing this problem.
Thanks in advance,
SIva