Re: Problem in Multiple table Join

This is a multi-part message in MIME format.


------_=_NextPart_001_01C8C803.FCFC8E25
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Use subselects=0D=0A =0D=0ASELECT up=2Euser_id, f=2Efriendcount,
i=2Eimagec=
ount, v=2Evideocount, a=2Eaudicount=0D=0AFROM user_profile up=0D=0ALEFT
JOI=
N (SELECT owner_user_id, COUNT(owner_user_id) As friendcount=0D=0AFROM
frie=
nd GROUP BY owner_user_id) As f ON up=2Euser_id =3D  f=2Eowner_user_id=0D=
=0ALEFT JOIN (SELECT owner_user_id, COUNT(user_id) As videocount=0D=0AFROM
=
video GROUP BY user_id) As v ON up=2Euser_id =3D  v=2Euser_id=0D=0ALEFT
JOI=
N (SELECT owner_user_id, COUNT(user_id) As imagecount=0D=0AFROM friend
GROU=
P BY owner_user_id) As i ON up=2Euser_id =3D  i=2Euser_id=0D=0A =0D=0Aetc=
=2E=2E=0D=0A =0D=0AWell hopefully you get the idea=2E=0D=0A =0D=0A =0D=0A=
=0D=0A________________________________=0D=0A=0D=0AFrom:
pgsql-novice-owner@[EMAIL PROTECTED]
 on behalf of siva c=0D=0ASent: Fri 6/6/2008 2:15
PM=0D=0AT=
o: pgsql-novice@[EMAIL PROTECTED]
 [NOVICE] Problem in
Multiple=
 table Join=0D=0A=0D=0A=0D=0A=0D=0AHi All,=0D=0A=0D=0A =0D=0A=0D=0AI have
4=
 tables namely User_profile , Friends, Video , Audio=2E =0D=0A=0D=0A
=0D=0A=
=0D=0AFriend, Image , Video and Audio table datas are related to Users=2E
A=
ll these tables holds Foreignkey to User_profile table=2E  I want to
genera=
te re****ts for user usage details=2E  =0D=0A=0D=0A =0D=0A=0D=0AWhen I
tried=
 to run simple independent SQL I am able to get the proper count for each
s=
ql output=2E =0D=0A=0D=0A =0D=0A=0D=0ASQL to generate User -- > friend
coun=
t for each user=0D=0A=0D=0A =0D=0A=0D=0Aselect
up=2Euser_id=0D=0A=0D=0Acoun=
t (f=2Eowner_user_id) as friendcount=0D=0A=0D=0Afrom user_profile
up=0D=0A=
=0D=0ALEFT OUTER JOIN friend f ON (up=2Euser_id =3D f=2Eowner_user_id
)=0D=
=0A=0D=0Agroup by up=2Euser_id order by user_id;=0D=0A=0D=0A
=0D=0A=0D=0ASQ=
L to generate User -- > video count for each user=0D=0A=0D=0A
=0D=0A=0D=0As=
elect up=2Euser_id=0D=0A=0D=0Acount (i=2Eowner_user_id) as
imagecount=0D=0A=
=0D=0Afrom user_profile up=0D=0A=0D=0ALEFT OUTER JOIN image i ON
(up=2Euser=
_id =3D i=2Eowner_user_id )=0D=0A=0D=0Agroup by up=2Euser_id order by
user_=
id;=0D=0A=0D=0A =0D=0A=0D=0A =0D=0A=0D=0ASQL to generate User -- > video
co=
unt for each user=0D=0A=0D=0A =0D=0A=0D=0Aselect
up=2Euser_id=0D=0A=0D=0Aco=
unt (v=2Eowner_user_id) as videocount=0D=0A=0D=0Afrom user_profile
up=0D=0A=
=0D=0ALEFT OUTER JOIN video v ON (up=2Euser_id =3D v=2Eowner_user_id )=0D=
=0A=0D=0Agroup by up=2Euser_id order by user_id;=0D=0A=0D=0A
=0D=0A=0D=0ASQ=
L to generate User -- > Audio count for each user=0D=0A=0D=0A
=0D=0A=0D=0As=
elect up=2Euser_id=0D=0A=0D=0Acount (a=2Eowner_user_id) as
videocount=0D=0A=
=0D=0Afrom user_profile up=0D=0A=0D=0ALEFT OUTER JOIN audio a ON
(up=2Euser=
_id =3D a=2Eowner_user_id )=0D=0A=0D=0Agroup by up=2Euser_id order by
user_=
id;=0D=0A=0D=0A =0D=0A=0D=0AI want to combine all these SQL into single
Que=
ry and want to generate singe output=2E But output data was wrong=2E The
Co=
unts are multiplied=2E =0D=0A=0D=0A =0D=0A=0D=0Aselect up=2Euser_id=0D=0A=
=0D=0A, count (f=2Eowner_user_id) as friendcount=0D=0A=0D=0A,count
(i=2Eown=
er_user_id) as imagecount=0D=0A=0D=0A, count(v=2Euser_id) as
videocount=0D=
=0A=0D=0A,count(a=2Euser_id) as audiocount=0D=0A=0D=0Afrom user_profile
up=
=0D=0A=0D=0ALEFT OUTER JOIN friend f ON (up=2Euser_id =3D
f=2Eowner_user_id=
 )=0D=0A=0D=0ALEFT OUTER JOIN image i ON (up=2Euser_id =3D
i=2Eowner_user_i=
d )=0D=0A=0D=0ALEFT OUTER  JOIN video s ON (up=2Euser_id =3D v=2Euser_id
)=
=0D=0A=0D=0ALEFT OUTER  JOIN audio a on (up=2Euser_id =3D a=2Euser_id)=0D=
=0A=0D=0Agroup by up=2Euser_id order by user_id;=0D=0A=0D=0A =0D=0A=0D=0A
=
=0D=0A=0D=0AI don't know what am I missing in the above SQL=2E It would be
=
great help if someone can help me in fixing this problem=2E=0D=0A=0D=0A
=0D=
=0A=0D=0AThanks in advance,=0D=0A=0D=0ASIva=0D=0A=0D=0A
=0D=0A=0D=0A=0D=0A=
=0D=0A=0D=0A-----------------------------------------=0D=0AThe substance
of=
 this message, including any attachments, may be=0Aconfidential, legally
pr=
ivileged and/or exempt from disclosure=0Apursuant to Massachusetts law=2E
I=
t is intended=0D=0Asolely for the addressee=2E If you received this in
erro=
r, please=0Acontact the sender and delete the material from any
computer=2E=
=0D=0A
------_=_NextPart_001_01C8C803.FCFC8E25
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<HTML dir=3Dltr><HEAD>=0A<META http-equiv=3DContent-Type
content=3D"text/ht=
ml; charset=3Dunicode">=0A<META content=3D"MSHTML 6=2E00=2E2900=2E3314"
nam=
e=3DGENERATOR></HEAD>=0A<BODY>=0A<DIV id=3DidOWAReplyText90984 dir=3Dltr>=
=0A<DIV dir=3Dltr><FONT face=3DArial color=3D#000000 size=3D2>Use
subselect=
s</FONT></DIV>=0A<DIV dir=3Dltr><FONT face=3DArial
size=3D2></FONT>&nbsp;</=
DIV>=0A<DIV dir=3Dltr><FONT face=3DArial size=3D2>SELECT up=2Euser_id,
f=2E=
friendcount, i=2Eimagecount, v=2Evideocount,
a=2Eaudicount</FONT></DIV>=0A<=
DIV dir=3Dltr><FONT face=3DArial size=3D2>FROM <FONT face=3D"Times New
Roma=
n" size=3D3>user_profile up</FONT></FONT></DIV>=0A<DIV dir=3Dltr>LEFT JOIN
=
(SELECT owner_user_id, COUNT(owner_user_id) As friendcount</DIV>=0A<DIV
dir=
=3Dltr>FROM friend GROUP BY owner_user_id) As f ON up=2Euser_id =3D&nbsp;
f=
=2Eowner_user_id</DIV>=0A<DIV dir=3Dltr>=0A<DIV dir=3Dltr>LEFT JOIN
(SELECT=
 owner_user_id, COUNT(user_id) As videocount</DIV>=0A<DIV
dir=3Dltr>FROM&nb=
sp;video GROUP BY user_id) As&nbsp;v ON up=2Euser_id =3D&nbsp;
v=2Euser_id<=
/DIV>=0A<DIV dir=3Dltr>=0A<DIV dir=3Dltr>LEFT JOIN (SELECT owner_user_id,
C=
OUNT(user_id) As imagecount</DIV>=0A<DIV dir=3Dltr>FROM friend GROUP BY
own=
er_user_id) As i ON up=2Euser_id =3D&nbsp; i=2Euser_id</DIV>=0A<DIV
dir=3Dl=
tr>&nbsp;</DIV>=0A<DIV dir=3Dltr>etc=2E=2E</DIV>=0A<DIV
dir=3Dltr>&nbsp;</D=
IV>=0A<DIV dir=3Dltr>Well hopefully you get the idea=2E</DIV></DIV></DIV>=
=0A<DIV dir=3Dltr><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>=0A<DIV
di=
r=3Dltr><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></DIV>=0A<DIV
dir=3D=
ltr><BR>=0A<HR tabIndex=3D-1>=0A<FONT face=3DTahoma size=3D2><B>From:</B>
p=
gsql-novice-owner@[EMAIL PROTECTED]
 on behalf of siva c<BR><B>Sent:</B> Fri
=
6/6/2008 2:15 PM<BR><B>To:</B>
pgsql-novice@[EMAIL PROTECTED]
><B>Subject:=
</B> [NOVICE] Problem in Multiple table Join<BR></FONT><BR></DIV>=0A<DIV>=
=0A<DIV class=3Dgmail_quote>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=
=3D3><FONT face=3DCalibri>Hi All,</FONT></FONT></P>=0A<P style=3D"MARGIN:
0=
in 0in 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P
style=3D"M=
ARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>I have 4 tables
nam=
ely User_profile , Friends, Video , Audio=2E </FONT></FONT></P>=0A<P
style=
=3D"MARGIN: 0in 0in 0pt"><FONT face=3DCalibri
size=3D3></FONT>&nbsp;</P>=0A=
<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=3DCalibri>Friend=
, Image , Video and Audio table datas are related to Users=2E All these
tab=
les holds Foreignkey to User_profile table=2E<SPAN>&nbsp; </SPAN>I want to
=
generate re****ts for user usage details=2E
<SPAN>&nbsp;</SPAN></FONT></FONT=
></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DCalibri
size=3D3></FO=
NT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=
=3DCalibri>When I tried to run simple independent SQL I am able to get the
=
proper count for each sql output=2E </FONT></FONT></P>=0A<P
style=3D"MARGIN=
: 0in 0in 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P style=
=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>SQL to
generat=
e User -- &gt; friend count for each user</FONT></FONT></P>=0A<P
style=3D"M=
ARGIN: 0in 0in 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P
st=
yle=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>select up=
=2Euser_id</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=
=3D3><FONT face=3DCalibri>count (f=2Eowner_user_id) as
friendcount</FONT></=
FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=3DCa=
libri>from user_profile up</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in
=
0pt"><FONT size=3D3><FONT face=3DCalibri>LEFT OUTER JOIN friend f ON
(up=2E=
user_id =3D f=2Eowner_user_id )</FONT></FONT></P>=0A<P style=3D"MARGIN:
0in=
 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>group by up=2Euser_id order
b=
y user_id;</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
face=
=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in
0pt"><F=
ONT size=3D3><FONT face=3DCalibri>SQL to generate User -- &gt; video count
=
for each user</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
fa=
ce=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in
0pt">=
<FONT size=3D3><FONT face=3DCalibri>select up=2Euser_id</FONT></FONT></P>=
=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=3DCalibri>cou=
nt (i=2Eowner_user_id) as imagecount</FONT></FONT></P>=0A<P
style=3D"MARGIN=
: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>from user_profile
up</FO=
NT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=
=3DCalibri>LEFT OUTER JOIN image i ON (up=2Euser_id =3D i=2Eowner_user_id
)=
</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
=
face=3DCalibri>group by up=2Euser_id order by
user_id;</FONT></FONT></P>=0A=
<P style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DCalibri
size=3D3></FONT>&nbsp=
;</P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DCalibri
size=3D3></FO=
NT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=
=3DCalibri>SQL to generate User -- &gt; video count for each
user</FONT></F=
ONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DCalibri
size=3D3><=
/FONT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
fa=
ce=3DCalibri>select up=2Euser_id</FONT></FONT></P>=0A<P style=3D"MARGIN:
0i=
n 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>count (v=2Eowner_user_id)
as=
 videocount</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=
=3D3><FONT face=3DCalibri>from user_profile up</FONT></FONT></P>=0A<P
style=
=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>LEFT OUTER
JOI=
N video v ON (up=2Euser_id =3D v=2Eowner_user_id )</FONT></FONT></P>=0A<P
s=
tyle=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>group by
u=
p=2Euser_id order by user_id;</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in
0=
in 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P
style=3D"MARGI=
N: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>SQL to generate User
--=
 &gt; Audio count for each user</FONT></FONT></P>=0A<P style=3D"MARGIN:
0in=
 0in 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P
style=3D"MAR=
GIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>select
up=2Euser_id</=
FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
fa=
ce=3DCalibri>count (a=2Eowner_user_id) as
videocount</FONT></FONT></P>=0A<P=
 style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>from
use=
r_profile up</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
siz=
e=3D3><FONT face=3DCalibri>LEFT OUTER JOIN audio a ON (up=2Euser_id =3D a=
=2Eowner_user_id )</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in
0pt"><FO=
NT size=3D3><FONT face=3DCalibri>group by up=2Euser_id order by
user_id;</F=
ONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT face=3DCalibri
siz=
e=3D3></FONT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=3D3><=
FONT face=3DCalibri>I want to combine all these SQL into single Query and
w=
ant to generate singe output=2E But output data was wrong=2E The Counts
are=
 multiplied=2E </FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
=
face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P style=3D"MARGIN: 0in 0in
0pt=
"><FONT size=3D3><FONT face=3DCalibri>select
up=2Euser_id</FONT></FONT></P>=
=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>,
c=
ount (f=2Eowner_user_id) as friendcount</FONT></FONT></P>=0A<P
style=3D"MAR=
GIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>,count
(i=2Eowner_use=
r_id) as imagecount</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in
0pt"><F=
ONT size=3D3><FONT face=3DCalibri>, count(v=2Euser_id) as
videocount</FONT>=
</FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT
face=3D=
Calibri>,count(a=2Euser_id) as audiocount</FONT></FONT></P>=0A<P
style=3D"M=
ARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>from user_profile
u=
p</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=3D3><FONT=
 face=3DCalibri>LEFT OUTER JOIN friend f ON (up=2Euser_id =3D
f=2Eowner_use=
r_id )</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=3D3>=
<FONT face=3DCalibri>LEFT OUTER JOIN image i ON (up=2Euser_id =3D
i=2Eowner=
_user_id )</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT
size=
=3D3><FONT face=3DCalibri>LEFT OUTER <SPAN>&nbsp;</SPAN>JOIN video s ON
(up=
=2Euser_id =3D v=2Euser_id )</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in
0i=
n 0pt"><FONT size=3D3><FONT face=3DCalibri>LEFT OUTER
<SPAN>&nbsp;</SPAN>JO=
IN audio a on (up=2Euser_id =3D a=2Euser_id)</FONT></FONT></P>=0A<P style=
=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>group by
up=2E=
user_id order by user_id;</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in 0in
0=
pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P style=3D"MARGIN:
0=
in 0in 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P
style=3D"M=
ARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>I don't know what
a=
m I missing in the above SQL=2E It would be great help if someone can help
=
me in fixing this problem=2E</FONT></FONT></P>=0A<P style=3D"MARGIN: 0in
0i=
n 0pt"><FONT face=3DCalibri size=3D3></FONT>&nbsp;</P>=0A<P
style=3D"MARGIN=
: 0in 0in 0pt"><FONT size=3D3><FONT face=3DCalibri>Thanks in
advance,</FONT=
></FONT></P>=0A<P style=3D"MARGIN: 0in 0in 0pt"><FONT size=3D3><FONT face=
=3DCalibri>SIva</FONT></FONT></P><FONT color=3D#888888>=0A<P
style=3D"MARGI=
N: 0in 0in 0pt"><FONT face=3DCalibri
size=3D3></FONT>&nbsp;</P></FONT></DIV=
><BR></DIV></BODY></HTML>=0D=0A<HTML><BODY><P><hr
size=3D1></P>=0D=0A<P><ST=
RONG>=0D=0AThe substance of this message, including any attachments, may
be=
 confidential, legally privileged and/or exempt from disclosure pursuant
to=
 Massachusetts law=2E It is intended solely for the addressee=2E If you
rec=
eived this in error, please contact the sender and delete the material
from=
 any computer=2E=0D=0A</STRONG></P></BODY></HTML>=0D=0A=0D=0A<P><hr
size=3D=
1></P>=0D=0A<P><STRONG><font size=3D"2" color=3D"339900"> Help make the
ear=
th a greener place=2E If at all possible resist printing this email and
joi=
n us in saving paper=2E </p> <p>
</font></STRONG></P>=0D=0A=0D=0A=0D=0A=0D=
=0A=0D=0A=0D=0A
------_=_NextPart_001_01C8C803.FCFC8E25--