Skip site navigation (1) Skip section navigation (2)

Problem in Multiple table Join

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Obe, ReginaDate: 2008-06-06 18:34:43
Subject: Re: Problem in Multiple table Join
Previous:From: John DeSoiDate: 2008-06-04 23:47:38
Subject: Re: PostgreSQL IN A WEB SITE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group