Re: Problem in Multiple table Join - Mailing list pgsql-novice
From | Obe, Regina |
---|---|
Subject | Re: Problem in Multiple table Join |
Date | |
Msg-id | 53F9CF533E1AA14EA1F8C5C08ABC08D20197A0AD@ZDND.DND.boston.cob Whole thread Raw |
In response to | Problem in Multiple table Join ("siva c" <sivanandame.c@gmail.com>) |
List | pgsql-novice |
From: pgsql-novice-owner@postgresql.org on behalf of siva c
Sent: Fri 6/6/2008 2:15 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] 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
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.
pgsql-novice by date: