Problem in Multiple table Join

------=_Part_7260_25443496.1212776120268
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 re****ts
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

------=_Part_7260_25443496.1212776120268
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div class="gmail_quote">
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">Hi
All,</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">I have
4 tables namely User_profile , Friends, Video , Audio. </font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">Friend,
Image , Video and Audio table datas are related to Users. All these tables
holds Foreignkey to User_profile table.<span>&nbsp; </span>I want to
generate re****ts for user usage details.
<span>&nbsp;</span></font></font></p>

<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">When I
tried to run simple independent SQL I am able to get the proper count for
each sql output. </font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">SQL to
generate User -- &gt; friend count for each user</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">select
up.user_id</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">count
(f.owner_user_id) as friendcount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">from
user_profile up</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER JOIN friend f ON (up.user_id = f.owner_user_id )</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">group
by up.user_id order by user_id;</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">SQL to
generate User -- &gt; video count for each user</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">select
up.user_id</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">count
(i.owner_user_id) as imagecount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">from
user_profile up</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER JOIN image i ON (up.user_id = i.owner_user_id )</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">group
by up.user_id order by user_id;</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">SQL to
generate User -- &gt; video count for each user</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">select
up.user_id</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">count
(v.owner_user_id) as videocount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">from
user_profile up</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER JOIN video v ON (up.user_id = v.owner_user_id )</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">group
by up.user_id order by user_id;</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">SQL to
generate User -- &gt; Audio count for each user</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">select
up.user_id</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">count
(a.owner_user_id) as videocount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">from
user_profile up</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER JOIN audio a ON (up.user_id = a.owner_user_id )</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">group
by up.user_id order by user_id;</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">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.
</font></font></p>

<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">select
up.user_id</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">, count
(f.owner_user_id) as friendcount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">,count
(i.owner_user_id) as imagecount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">,
count(v.user_id) as videocount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font
face="Calibri">,count(a.user_id) as audiocount</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">from
user_profile up</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER JOIN friend f ON (up.user_id = f.owner_user_id )</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER JOIN image i ON (up.user_id = i.owner_user_id )</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER <span>&nbsp;</span>JOIN video s ON (up.user_id = v.user_id
)</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">LEFT
OUTER <span>&nbsp;</span>JOIN audio a on (up.user_id =
a.user_id)</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">group
by up.user_id order by user_id;</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">I
don&#39;t know what am I missing in the above SQL. It would be great help
if someone can help me in fixing this problem.</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font face="Calibri">Thanks
in advance,</font></font></p>
<p style="MARGIN: 0in 0in 0pt"><font size="3"><font
face="Calibri">SIva</font></font></p><font color="#888888">
<p style="MARGIN: 0in 0in 0pt"><font face="Calibri"
size="3">&nbsp;</font></p></font></div><br>

------=_Part_7260_25443496.1212776120268--