Double SQL Join Problem through ODBC

I have experienced problems issuing SQL containing multiple joins through an ODBC connection. I read a thread here, which suggested an alternative to using multiple joins, this I tried and it appears to work. The code is as follows:

SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, MAX(CASE WHEN SalesRep.Rowid = tblCust.SalesMan THEN SalesRep.Name END) AS SalesManNa, MAX(CASE WHEN SalesRep.Rowid = tblCust.AccManager THEN SalesRep.Name END) AS AccManNa
FROM Customers tblCust INNER JOIN SalesRep ON tblCust.SalesMan = SalesRep.Rowid OR tblCust.AccManager = SalesRep.Rowid
WHERE (RowID = '1126318')
GROUP BY tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager

However, I would like to know how efficient such code is compared to my original (and problematic) joining.

The following is a summary of my original problem

I have one view (Customers) and a table (SalesRep). Customers contains a list of customer companies, while SalesRep lists internal staff. Each company record has fields to identify a Sales contact and an Account Manager, both of which would be listed within the SalesRep table and could be the same person.

It seems fairly straightforward, we are using two INNER JOIN's between the tables, as follows simplified query shows:

SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, salesRepTbl1.Name AS SalesManNa, salesRepTbl2.Name AS AccManNa
FROM Customers tblCust
INNER JOIN SalesRep salesRepTbl1 ON tblCust.SalesMan = salesRepTbl1.Rowid
INNER JOIN SalesRep salesRepTbl2 ON tblCust.AccManager = salesRepTbl2.Rowid
WHERE (tblCust.Rowid = '1126318')

This works fine when executed on SQL Server, as following result:

AccountNo--SalesMan--AccManager--SalesManNa--AccManNa
1234567--100--106--John Smith--Carol Flood

However, when issued using ASP through an ODBC connection to an Intersystems Cache database - SalesManNa is showing the result expected for AccManNa, as...

AccountNo--SalesMan--AccManager--SalesManNa--AccManNa
1234567--100--106--Carol Flood--Carol Flood

Interestingly, the above query does not return any results when executed through Vis Web Dev 2005 Express.

We are using a DSN for the connection, which uses the InterSystems Cache ODBC driver (5.2.0.329.0). I have tried many variations on the join and different syntax but no success and I'm not privy to any specific ODBC restrictions.

Any pointers as to what may be causing the problem through ODBC would be greatly appreciated as would any opinion of the efficiency of the working solution.

Many thanks,
CliffSELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, salesRepTbl1.Name AS SalesManNa, salesRepTbl2.Name AS AccManNa
FROM Customers tblCust
INNER JOIN SalesRep salesRepTbl1 ON tblCust.SalesMan = salesRepTbl1.Rowid
INNER JOIN SalesRep salesRepTbl2 ON tblCust.AccManager = salesRepTbl2.Rowid
WHERE (tblCust.Rowid = '1126318')
In terms of performance, I would suggest to try the following instead:SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager,
s1.Name AS SalesManNa, s2.Name AS AccManNa
FROM (SELECT AccManager, SalesMan
FROM Customers
WHERE Rowid = '1126318') AS tblCust
INNER JOIN SalesRep s1 ON tblCust.SalesMan = s1.Rowid
INNER JOIN SalesRep s2 ON tblCust.AccManager = s2.Rowid(Sorry, I've no hands-on experience with SqlServer, so I might be wrong about the performance improvement.)|||Many thanks for your reply Peter.

I have tried your suggestion and similar to my own 2x INNER JOIN code, it also returns incorrect data when ran through ASP to Cache ODBC data source...it gets the correct salesman and account manager ids from the Customer table but the join to the SalesRep table returns the account manager name for both!|||I believe that your original code executed on SQL Server would be slightly more efficient, however there are often problems with ODBC drivers which are reluctant to join to the same table more than once.... I think it is an issue with labelling tables within joins... and so you're original SQL code would not return the correct values through the ODBC connection.

I think you're amended code would be the most efficient way to get this resultant recordset using the current ODBC connection.

:)|||The most efficient way, as always, is to create this as a view or stored procedure. I'm betting that would solve your mysterious problem as well.|||I used to date a girl that was double jointed

Domain Recommendations.

Has anyone experienced any SQL difficulties resulting from removing Domain
Admins' permissions from a SQL Server 2k machine. We are toying with the
idea now, mainly to keep the network people out of SQL. Our other option is
to remove the SQL Server from the domain itself. Any advice or information
would be appreciated.
Thanks for your time,
JoeJoe
Very useful site.
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --secu
rity best practices
"Joe" <nospam@.nospam.com> wrote in message
news:ORXpZyykDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Has anyone experienced any SQL difficulties resulting from removing Domain
> Admins' permissions from a SQL Server 2k machine. We are toying with the
> idea now, mainly to keep the network people out of SQL. Our other option
is
> to remove the SQL Server from the domain itself. Any advice or
information
> would be appreciated.
> Thanks for your time,
>
> Joe
>|||Very nice. Thanks for the info.
Joe
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O#6y73ykDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Joe
> Very useful site.
>
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --secu
> rity best practices
>
> "Joe" <nospam@.nospam.com> wrote in message
> news:ORXpZyykDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Has anyone experienced any SQL difficulties resulting from removing
Domain
> > Admins' permissions from a SQL Server 2k machine. We are toying with
the
> > idea now, mainly to keep the network people out of SQL. Our other
option
> is
> > to remove the SQL Server from the domain itself. Any advice or
> information
> > would be appreciated.
> >
> > Thanks for your time,
> >
> >
> > Joe
> >
> >
>|||To keep the network folks out of SQL Server (but not the
box itself) you can remove the BUILT/Administrators login
from the SQL Server.
This will remove access to SQL Server for any local
administrators (Domain Admins included). You may want to
add your domain account to the server administrators role
so that you continue to have sa privileges.
Tim
>--Original Message--
>Has anyone experienced any SQL difficulties resulting
from removing Domain
>Admins' permissions from a SQL Server 2k machine. We
are toying with the
>idea now, mainly to keep the network people out of SQL.
Our other option is
>to remove the SQL Server from the domain itself. Any
advice or information
>would be appreciated.
>Thanks for your time,
>
>Joe
>
>.
>|||Is there a danger in removing thier access to the box itself? They have
fried it 3 days in a row.
Thanks,
Joe
"Tim Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:005f01c39330$66bb73a0$a301280a@.phx.gbl...
> To keep the network folks out of SQL Server (but not the
> box itself) you can remove the BUILT/Administrators login
> from the SQL Server.
> This will remove access to SQL Server for any local
> administrators (Domain Admins included). You may want to
> add your domain account to the server administrators role
> so that you continue to have sa privileges.
> Tim
>
> >--Original Message--
> >Has anyone experienced any SQL difficulties resulting
> from removing Domain
> >Admins' permissions from a SQL Server 2k machine. We
> are toying with the
> >idea now, mainly to keep the network people out of SQL.
> Our other option is
> >to remove the SQL Server from the domain itself. Any
> advice or information
> >would be appreciated.
> >
> >Thanks for your time,
> >
> >
> >Joe
> >
> >
> >.
> >|||Joe,
It's hard for me to say. I've never tried it. I
wouldn't think that it would affect the functioning of
SQL Server unless the service account is configured as a
domain admin. It could affect other things though. I
would only try it on a test machine until you know for
sure.
One thing though, I would imagine that THE Domain
Administrator (as opposed to the Domain Admins group)
will always have access to the box. If you are having
problems with the network folks and are trying to lock
them out, they can probably find their way back in.
Tim
>--Original Message--
>Is there a danger in removing thier access to the box
itself? They have
>fried it 3 days in a row.
>Thanks,
>Joe
>
>"Tim Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:005f01c39330$66bb73a0$a301280a@.phx.gbl...
>> To keep the network folks out of SQL Server (but not
the
>> box itself) you can remove the BUILT/Administrators
login
>> from the SQL Server.
>> This will remove access to SQL Server for any local
>> administrators (Domain Admins included). You may want
to
>> add your domain account to the server administrators
role
>> so that you continue to have sa privileges.
>> Tim
>>
>> >--Original Message--
>> >Has anyone experienced any SQL difficulties resulting
>> from removing Domain
>> >Admins' permissions from a SQL Server 2k machine. We
>> are toying with the
>> >idea now, mainly to keep the network people out of
SQL.
>> Our other option is
>> >to remove the SQL Server from the domain itself. Any
>> advice or information
>> >would be appreciated.
>> >
>> >Thanks for your time,
>> >
>> >
>> >Joe
>> >
>> >
>> >.
>> >
>
>.
>|||There is nothing wrong about removing BUILTIN\Administrators. Of course, the current administrators
using this will complain, but this is what you are after, right?
Just make sure you add some other group first (assuming you use Windows logins in the first place).
I usually create some SQLAdmins group or similar and add that first.
There's a reason why Administrators is there in the first place. Imagine you install SQL Server in
Windows Only mode. No-one would be able to login if it wasn't for the Administrators group being
there.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Joe" <nospam@.nospam.com> wrote in message news:uytKhGzkDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Is there a danger in removing thier access to the box itself? They have
> fried it 3 days in a row.
> Thanks,
> Joe
>
> "Tim Richardson" <anonymous@.discussions.microsoft.com> wrote in message
> news:005f01c39330$66bb73a0$a301280a@.phx.gbl...
> > To keep the network folks out of SQL Server (but not the
> > box itself) you can remove the BUILT/Administrators login
> > from the SQL Server.
> >
> > This will remove access to SQL Server for any local
> > administrators (Domain Admins included). You may want to
> > add your domain account to the server administrators role
> > so that you continue to have sa privileges.
> >
> > Tim
> >
> >
> > >--Original Message--
> > >Has anyone experienced any SQL difficulties resulting
> > from removing Domain
> > >Admins' permissions from a SQL Server 2k machine. We
> > are toying with the
> > >idea now, mainly to keep the network people out of SQL.
> > Our other option is
> > >to remove the SQL Server from the domain itself. Any
> > advice or information
> > >would be appreciated.
> > >
> > >Thanks for your time,
> > >
> > >
> > >Joe
> > >
> > >
> > >.
> > >
>