From: | "siva c" <sivanandame(dot)c(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Problem in Multiple table Join |
Date: | 2008-06-06 18:15:20 |
Message-ID: | b540b6f50806061115k37ba658nf390764bbc91a93f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Obe, Regina | 2008-06-06 18:34:43 | Re: Problem in Multiple table Join |
Previous Message | John DeSoi | 2008-06-04 23:47:38 | Re: PostgreSQL IN A WEB SITE |