Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Unanswered: 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,
    Cliff

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by cliffajw
    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')
    In terms of performance, I would suggest to try the following instead:
    Code:
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2007
    Posts
    2
    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!

  4. #4
    Join Date
    Feb 2007
    Posts
    1

    SQL Performance

    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.


  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I used to date a girl that was double jointed
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •