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
KKPTry 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.|||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|||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|||You'd code it like this

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.|||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|||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|||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

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.