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

Re: Problem in Multiple table Join

From: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
To: "siva c" <sivanandame(dot)c(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Problem in Multiple table Join
Date: 2008-06-06 18:34:43
Message-ID: 53F9CF533E1AA14EA1F8C5C08ABC08D20197A0AD@ZDND.DND.boston.cob (view raw or flat)
Thread:
Lists: pgsql-novice
Use subselects
 
SELECT up.user_id, f.friendcount, i.imagecount, v.videocount, a.audicount
FROM user_profile up
LEFT JOIN (SELECT owner_user_id, COUNT(owner_user_id) As friendcount
FROM friend GROUP BY owner_user_id) As f ON up.user_id =  f.owner_user_id
LEFT JOIN (SELECT owner_user_id, COUNT(user_id) As videocount
FROM video GROUP BY user_id) As v ON up.user_id =  v.user_id
LEFT JOIN (SELECT owner_user_id, COUNT(user_id) As imagecount
FROM friend GROUP BY owner_user_id) As i ON up.user_id =  i.user_id
 
etc..
 
Well hopefully you get the idea.
 
 

________________________________

From: pgsql-novice-owner(at)postgresql(dot)org on behalf of siva c
Sent: Fri 6/6/2008 2:15 PM
To: pgsql-novice(at)postgresql(dot)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.

In response to

pgsql-novice by date

Next:From: Patricia MitchellDate: 2008-06-10 14:18:05
Subject: Question:
Previous:From: siva cDate: 2008-06-06 18:15:20
Subject: Problem in Multiple table Join

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