Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: Outer join problem

    This is the query I m executing.


    SELECT M.MetroName Metro, R.Description Bank,
    B.BranchCode + ' - ' + B.BranchName Branch, E.EmployeeLastName
    Name FROM Metro M, Region R, Branch B, Employee E where
    E.BranchID *= B.BranchID AND B.RegionID *= R.RegionID AND
    R.MetroID *= M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber
    Is Not Null


    The error message I m getting is
    Server: Msg 301, Level 16, State 1, Line 1
    Query contains an outer-join request that is not permitted.

    Try to help me asap.

    Thx in advance
    KKP

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try to replace *= with LEFT OUTER JOIN. The issue there deals with the optimizer complaining about your request to LEFT JOIN a table that has already been LEFT JOINed.

  3. #3
    Join Date
    May 2003
    Posts
    40
    Originally posted by rdjabarov
    Try to replace *= with LEFT OUTER JOIN. The issue there deals with the optimizer complaining about your request to LEFT JOIN a table that has already been LEFT JOINed.
    Hi Rdja,

    I tried that I made this as it is

    SELECT M.MetroName Metro, R.Description Bank,
    B.BranchCode + ' - ' + B.BranchName Branch, E.EmployeeLastName
    Name FROM Metro M, Region R, Branch B, Employee E where
    E.BranchID LEFT OUTER JOIN B.BranchID AND B.RegionID LEFT OUTER JOIN R.RegionID AND
    R.MetroID LEFT OUTER JOIN M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber
    Is Not Null


    It gives back me the error
    Server: Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'LEFT'.

    Can u solve this?

    Thx
    KKP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to look up and review the syntax of SELECT statements in Books Online.

    SELECT M.MetroName Metro,
    R.Description Bank,
    B.BranchCode + ' - ' + B.BranchName Branch,
    E.EmployeeLastName Name
    FROM Metro M
    left outer join Region R on M.MetroID = R.MetroID
    left outer join Branch B on R.RegionID = B.RegionID
    left outer join Employee E on B.BranchID = E.BranchID
    where E.ProductionNumber = '0000'
    AND E.ProductionNumber Is Not Null

    blindman

  5. #5
    Join Date
    Jul 2002
    Posts
    58
    You'd code it like this

    Code:
    SELECT M.MetroName Metro,
           R.Description 'Bank',
           B.BranchCode + ' - ' + B.BranchName Branch,
           E.EmployeeLastName 'Name'
       FROM Metro AS M
       LEFT OUTER JOIN Region AS R ON R.MetroID = M.MetroID
       LEFT OUTER JOIN Branch AS B ON B.RegionID = R.RegionID
       LEFT OUTER JOIN Employee AS E ON E.BranchID = B.BranchID
       WHERE E.ProductionNumber = '0000'
             AND E.ProductionNumber IS NOT NULL
    But that WHERE clause basically undoes the effect of all those LEFT OUTER JOINS so I not sure what you're trying to accomplish here. I mean it seems pointless to use LEFT OUTER to preserve the rows in the Metro, Region, and Branch tables that don't have matching Employee rows if you're going to turn around and throw them away with the where clause. Besides, the two conditions E.ProductionNumber = '0000' and E.ProductionNumber IS NOT NULL are redundant. You don't need the later, as if E.ProductionNumber is '0000', it is of necessity not null.

    Can you explain your desired result? That might help us translate it into the correct SQL, as we really don't know what it is you're trying to get.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, logically you should either convert your outer joins to inner joins, or move the criteria to the join as well:

    SELECT M.MetroName Metro,
    R.Description Bank,
    B.BranchCode + ' - ' + B.BranchName Branch,
    E.EmployeeLastName Name
    FROM Metro M
    left outer join Region R on M.MetroID = R.MetroID
    left outer join Branch B on R.RegionID = B.RegionID
    left outer join Employee E on B.BranchID = E.BranchID
    and E.ProductionNumber = '0000'
    and E.ProductionNumber Is Not Null

  7. #7
    Join Date
    May 2003
    Posts
    40
    Steve and Blindman

    Thx for the reply.
    Steve : My objective of the query is to select Metroname, Description,
    Lastname and Branch code , name from four diff tables based on the
    criterias MetroIds, RegionIds and BranchIds. Reg the production number
    it is a four digit number in Employee table with diff values.
    I should take both factors '0000' and null so I added both the constraints in the where clause.

    You see my first query and try to tell me the error.

    SELECT M.MetroName Metro, R.Description Bank, B.BranchCode + ' - ' + B.BranchName Branch,
    E.EmployeeLastName Name FROM Metro M, Region R, Branch B, Employee E WHERE
    E.BranchID *= join B.BranchID AND B.RegionID *= R.RegionID AND R.MetroID *= M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber Is Not Null

    Error mesg : Server: Msg 301, Level 16, State 1, Line 1
    Query contains an outer-join request that is not permitted.

    so I added left outer joins. Lemme know if I have confused you more.
    waiting for ur reply.


    Thx
    KK

  8. #8
    Join Date
    Jul 2002
    Posts
    58
    AH, OK, I think I have it now.

    I think you need IS NULL instead of IS NOT NULL. Also, since you're using LEFT OUTER joins, those selection criteria need to be in the JOIN clause. I think you want something like tihis

    Code:
    SELECT M.MetroName Metro,
           R.Description 'Bank',
           B.BranchCode + ' - ' + B.BranchName Branch,
           E.EmployeeLastName 'Name'
       FROM Metro AS M
       LEFT OUTER JOIN Region AS R ON R.MetroID = M.MetroID
       LEFT OUTER JOIN Branch AS B ON B.RegionID = R.RegionID
       LEFT OUTER JOIN Employee AS E ON E.BranchID = B.BranchID
                                        AND (E.ProductionNumber = '0000'
                                             OR E.ProductionNumber IS NULL)
    Whether to put a condition like column IS NULL in the WHERE or JOIN clause depends on what you're trying to achieve. To use an OUTER JOIN to find rows that don't have matches in another table, you typically put the condition in a WHERE clause and use a column that won't naturally have NULLs. If, as I think you are saying you need, you only want to consider joining rows that have NULL (or possibly other values as well) but still need to preserve the previous tables even if there isn't a qualifying match, then you need to put the condition in the JOIN clause. This will make the OUTER JOIN function correctly and return nulls for the final table, even if it has rows that would typically match a normal join, but don't match you extra criteria.
    Last edited by Steve Duncan; 12-22-03 at 15:14.

Posting Permissions

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