outer join question 2

[butler]

Hi all,

I am having trouble getting an outer join to work. I have a working procedure that generates the following SQL statement and returns the results. It lists matching items from the 'Pointer' table. I need to modify it to return all items from the UserFavorite.ReportID, even if there is not a match. Does anyone have any suggestions??? I've tried a everything I can think of but keep getting "The table 'Pointer' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause."

SELECT
User.UserID,
UserFavorite.ReportID,
UserWare.WarehouseID,
UserDept.DepartmentID,
Pointer.ymd,
Pointer.Fname,
Department.Path,
Report.Description,
Report.ReportCount
FROM
User,
UserWare,
UserDept,
UserFavorite,
Pointer,
Department,
Report
WHERE
User.UserID = 'anyUserID' AND
Pointer.ymd = 20010723 AND
UserWare.UserID = User.UserID AND
UserDept.UserID = User.UserID AND
UserFavorite.UserID = User.UserID AND
UserWare.WarehouseID = Pointer.WarehouseID AND
UserDept.DepartmentID = Pointer.DepartmentID AND
UserFavorite.ReportID = Pointer.ReportID AND
Department.DepartmentID = Pointer.DepartmentID AND
Report.ReportID = Pointer.ReportID

 

[foxdev]

From the sql-server-performance.com quiz, I learned that WHERE clauses with IS NULL/IS NOT NULL must perform table scans to resolve the NULL/NOT NULL portion of the filter. So, some slowdown can be expected, even with an index.

And stars to both for your dogged determination! Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[butler]

THANKS everyone for all the help! I was finaly able to get things working!!! What I ended up doing as using DISTINCT with IN() and attaching it to the origional query with a UNION. This runs about 5 times faster than using the outer join with the IS NULL. It also eliminates my duplicates problem!! Here's how it turned out:

SELECT
Pointer.ymd, Pointer.WarehouseID, Pointer.DepartmentID, Pointer.ReportID, Pointer.Fname,
Department.Path,
Report.Description, Report.ReportCount
FROM ((
User INNER JOIN UserWare ON User.UserID = UserWare.UserID
INNER JOIN UserDept ON User.UserID = UserDept.UserID
INNER JOIN UserFavorite ON User.UserID = UserFavorite.UserID)
INNER JOIN Pointer ON UserWare.WarehouseID = Pointer.WarehouseID AND
UserDept.DepartmentID = Pointer.DepartmentID AND
UserFavorite.ReportID = Pointer.ReportID)
INNER JOIN Report ON Pointer.ReportID = Report.ReportID
INNER JOIN Department ON Pointer.DepartmentID = Department.DepartmentID
WHERE
User.UserID = 'wji' AND
Pointer.ymd = 20010627
UNION
SELECT
NULL, NULL, NULL, UserFavorite.ReportID, NULL, NULL, NULL, NULL
FROM UserFavorite
WHERE UserFavorite.ReportID NOT IN (
SELECT
DISTINCT UserFavorite.ReportID
FROM ((
User INNER JOIN UserWare ON User.UserID = UserWare.UserID
INNER JOIN UserDept ON User.UserID = UserDept.UserID
INNER JOIN UserFavorite ON User.UserID = UserFavorite.UserID)
INNER JOIN Pointer ON UserWare.WarehouseID = Pointer.WarehouseID AND
UserDept.DepartmentID = Pointer.DepartmentID AND
UserFavorite.ReportID = Pointer.ReportID)
INNER JOIN Report ON Pointer.ReportID = Report.ReportID
INNER JOIN Department ON Pointer.DepartmentID = Department.DepartmentID
WHERE
User.UserID = 'wji' AND
Pointer.ymd = 20010627)
ORDER BY Pointer.ymd, Pointer.WarehouseID, Pointer.ReportID;

 

[rajeevnandanmishra]

Hi butler,
It is good that you found out the required solution.
But what i had seen in my SQL experience is that running a union and /or distinct just over an IS NULL is more costly to the system.

Anyway, the solution works, that is great!