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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

  From Date Subject
Next Message Patricia Mitchell 2008-06-10 14:18:05 Question:
Previous Message siva c 2008-06-06 18:15:20 Problem in Multiple table Join