Re: Problem in Multiple table Join

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