3 tables query

I hable that has a ProOnwerID and ProFinOwner, when I create the following
view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
ProOwer shows up.. Any Idea why ?... is it because I have two ID for
Clients en same table ?
SELECT dbo.projects.*, dbo.employee.EmpName AS EmpName,
dbo.Clients.ClientName AS ProOwner, dbo.Clients.ClientName AS FinOwner
FROM dbo.projects LEFT OUTER JOIN
dbo.employee ON dbo.projects.ProPMID =
dbo.employee.EmpUserID LEFT OUTER JOIN
dbo.Clients ON dbo.projects.ProFinOwner =
dbo.Clients.ClientId AND dbo.projects.ProOwner = dbo.Clients.ClientId
thanksOn Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:

>I hable that has a ProOnwerID and ProFinOwner, when I create the following
>view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
>ProOwer shows up.. Any Idea why ?... is it because I have two ID for
>Clients en same table ?
Hi Carlos,
Your query tries to find ONE row in Clients that is equal to both the
ProFinOwner and the ProOwner. This will only succeed if ProOwner and
ProFinOwner are the same. If they are not, you'll get NULL (due to the
left join - with inner join, you'd not have gotten any rows at all).
I'm actually quite surprised that you did see the ProOwner when you
"deleted FinOwner" - but maybe I'm just misunderstanding what you actually
did.
Anyway, to show the name of the two owners, even if they are not the same,
you'll have to join in the client table twice:
SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
e.EmpName AS EmpName,
o.ClientName AS ProOwner,
po.ClientName AS FinOwner
FROM dbo.projects AS p
LEFT OUTER JOIN dbo.employee AS e
ON p.ProPMID = e.EmpUserID
LEFT OUTER JOIN dbo.Clients AS po
ON p.ProOwner = po.ClientId
LEFT OUTER JOIN dbo.Clients AS fo
ON p.ProFinOwner = fo.ClientId
If I were you, I'd also check if you really need all these joins to be
outer joins. Inner joins are usually faster.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo that did it !!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:j8ai01lfqn0j5c0l8tig70ne1tu1dqg576@.
4ax.com...
> On Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:
>
> Hi Carlos,
> Your query tries to find ONE row in Clients that is equal to both the
> ProFinOwner and the ProOwner. This will only succeed if ProOwner and
> ProFinOwner are the same. If they are not, you'll get NULL (due to the
> left join - with inner join, you'd not have gotten any rows at all).
> I'm actually quite surprised that you did see the ProOwner when you
> "deleted FinOwner" - but maybe I'm just misunderstanding what you actually
> did.
> Anyway, to show the name of the two owners, even if they are not the same,
> you'll have to join in the client table twice:
> SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
> e.EmpName AS EmpName,
> o.ClientName AS ProOwner,
> po.ClientName AS FinOwner
> FROM dbo.projects AS p
> LEFT OUTER JOIN dbo.employee AS e
> ON p.ProPMID = e.EmpUserID
> LEFT OUTER JOIN dbo.Clients AS po
> ON p.ProOwner = po.ClientId
> LEFT OUTER JOIN dbo.Clients AS fo
> ON p.ProFinOwner = fo.ClientId
> If I were you, I'd also check if you really need all these joins to be
> outer joins. Inner joins are usually faster.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)