an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

This exception is almost killing me today !!

an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

after try and tests and resolves, wiki helps me..

Alternatives

The effect of an outer join can also be obtained using a UNION ALL between an INNER JOIN and a SELECT of the rows in the "main" table that do not fulfill the join condition. For example
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
can also be written as
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID
 
UNION ALL
 
SELECT employee.LastName, employee.DepartmentID, CAST(NULL AS VARCHAR(20))
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

looks easy, turning back, maybe i should have became calmer and more relax, which should be all the case !!

so simply, overall, use less outer join !!!!! when must there is outer join, cannot mix regular join there.

http://en.wikipedia.org/wiki/Join_(SQL)

Advertisements

Author: lwpro2

Java J2EE professional

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s