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)

2005 install ques re owner of database?

Hello -
I just installed Sql Server 2005 on a company server. I copied and attached
database mdf and ldf files. The database runs okay with my application on
my developer machine. I just started setting up security on the server and
it is indicating the owner of the database is "MyOrganization"\MyName. My
login name (user name) on the network is MyName. I tried to change to chang
e
the Owner of the database under Files and I'm getting the message:
...The login 'MyName' does not exist on this server.
How can I fix this?
Any help will be greatly appreciated!
--
SandyHow do you connect to the company server? If you connect via SQL login, then
execute the below should do it (assuming the sql login is part of sysadmin).
exec <the_database_name>..sp_changedbowner 'the_sql_login'
If you're login as a NT user/acct and is part of the administrators group,
your NT user name is implicitly granted permission. If you really want to
use your NT user name/acct as the db_owner, you will have to explicitly add
your NT acct to sql sysadmin role.
e.g.
create login [domain\nt_acct] from windows;
exec sp_addsrvrolemember 'domain\nt_acct', 'sysadmin'
then you can change dbowner
exec <the_database_name>..sp_changedbowner 'domain\nt_acct'
-oj
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:CCFAE0DB-7208-4189-B9B4-F5A3B1EDCD0B@.microsoft.com...
> Hello -
> I just installed Sql Server 2005 on a company server. I copied and
> attached
> database mdf and ldf files. The database runs okay with my application on
> my developer machine. I just started setting up security on the server
> and
> it is indicating the owner of the database is "MyOrganization"\MyName. My
> login name (user name) on the network is MyName. I tried to change to
> change
> the Owner of the database under Files and I'm getting the message:
> ...The login 'MyName' does not exist on this server.
> How can I fix this?
> Any help will be greatly appreciated!
> --
> Sandy

2005 install ques re owner of database?

Hello -
I just installed Sql Server 2005 on a company server. I copied and attached
database mdf and ldf files. The database runs okay with my application on
my developer machine. I just started setting up security on the server and
it is indicating the owner of the database is "MyOrganization"\MyName. My
login name (user name) on the network is MyName. I tried to change to change
the Owner of the database under Files and I'm getting the message:
...The login 'MyName' does not exist on this server.
How can I fix this?
Any help will be greatly appreciated!
--
SandyHow do you connect to the company server? If you connect via SQL login, then
execute the below should do it (assuming the sql login is part of sysadmin).
exec <the_database_name>..sp_changedbowner 'the_sql_login'
If you're login as a NT user/acct and is part of the administrators group,
your NT user name is implicitly granted permission. If you really want to
use your NT user name/acct as the db_owner, you will have to explicitly add
your NT acct to sql sysadmin role.
e.g.
create login [domain\nt_acct] from windows;
exec sp_addsrvrolemember 'domain\nt_acct', 'sysadmin'
then you can change dbowner
exec <the_database_name>..sp_changedbowner 'domain\nt_acct'
-oj
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:CCFAE0DB-7208-4189-B9B4-F5A3B1EDCD0B@.microsoft.com...
> Hello -
> I just installed Sql Server 2005 on a company server. I copied and
> attached
> database mdf and ldf files. The database runs okay with my application on
> my developer machine. I just started setting up security on the server
> and
> it is indicating the owner of the database is "MyOrganization"\MyName. My
> login name (user name) on the network is MyName. I tried to change to
> change
> the Owner of the database under Files and I'm getting the message:
> ...The login 'MyName' does not exist on this server.
> How can I fix this?
> Any help will be greatly appreciated!
> --
> Sandy