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

 

[tlbroadbent]

Try the following. The parentheses are very important. Make sure you utilize them in the query.

You'll note that I converted the syntax to ANSI standard join syntax. I believe it is good practice to use the standard syntax in queries even if Microsoft examples fail to do so.

SELECT
User.UserID,
UserFavorite.ReportID,
UserWare.WarehouseID,
UserDept.DepartmentID,
Pointer.ymd,
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)
Left Join (Pointer
Inner Join Department On Pointer.DepartmentID=Department.DepartmentID
Inner Join Report Pointer.ReportID=Report.ReportID)
On UserWare.WarehouseID = Pointer.WarehouseID
And UserDept.DepartmentID = Pointer.DepartmentID
And UserFavorite.ReportID = Pointer.ReportID

WHERE
User.UserID = 'anyUserID' AND
Pointer.ymd = 20010723 Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rajeevnandanmishra]

Hi butler,
Try following
-----------------------
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
-----------------------

Currently i had used *= for left outer join (it is just for the compatibility with previous version). Try this, and if it works let me know, i will try to give the solution with actual left outer join keyword.

 

[butler]

Thanks much tlbroadbent,

I tried this but got the error "The correlation name 'Pointer' has the same exposed name as table 'Pointer'."

Thanks much rajeevnandanmishra,

I tried the *= but got "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."

????????

 

[rajeevnandanmishra]

Hmmm!!
OK try out this one.

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


Hope it works!!!

 

[tlbroadbent]

I wish it were possible to test these solutions before posting them. However, how about another try? Looks like I left out the word ON in one of the joins. Change the From clause as follows.

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)
Left Join (Pointer
Inner Join Department On Pointer.DepartmentID=Department.DepartmentID
Inner Join Report On Pointer.ReportID=Report.ReportID)
On UserWare.WarehouseID = Pointer.WarehouseID
And UserDept.DepartmentID = Pointer.DepartmentID
And UserFavorite.ReportID = Pointer.ReportID
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[butler]

Thanks again tlbroadbent and rajeevnandanmishra,

tlbroadbent, I found and added the 'on' in the From clause. And rajeevnandanmishra, I tried your post. The error messages are gone but both statements return 0 records. I am taking a closer look.

Thanks for the help! Let me know if you have ANYTHING to try!!!!

Thanks
--bill

 

[rajeevnandanmishra]

Hi butler,
Sorry, but try to exclude
WHERE User.UserID = 'anyUserID' AND
Pointer.ymd = 20010723
From your query and then check the result.

 

[butler]

Correction.

Both statements return the same results at the origional statement. (I forgot to change the 'anyUserID' back to a valid user). There is a record one record in UserFavorite that has no match in Pointer, but it is still not returned...

 

[tlbroadbent]

Does the record in UserFavorite have a matching record in User? Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[butler]

Yes, 6 records are currently being returned.

There are 3 records in UserFavorite for the user I am testing. On specified date the first two records match three records each in the Pointer table. The third record in UserFavorite has no match in the Pointer table for this user and date...

 

[rajeevnandanmishra]

Hi butler,
SQL only return the data for where clause is true. So, i don't know why you are putting Pointer.ymd = 20010723 in your where clause. Still, if you want to have the third row for which there is no user and date in pointer table, then run this
SELECT
User.UserID,
UserFavorite.ReportID,
UserWare.WarehouseID,
UserDept.DepartmentID,
Pointer.ymd,
Pointer.Fname,
Department.Path,
Report.Description,
Report.ReportCount
FROM
User JOIN UserWare ON User.UserID = UserWare.UserID
JOIN UserDept ON User.UserID = UserDept.UserID
JOIN UserFavorite ON User.UserID = UserFavorite.UserID
LEFT OUTER JOIN Pointer
ON UserFavorite.ReportID = Pointer.ReportID AND
Pointer.ymd = 20010723 AND
UserWare.WarehouseID = Pointer.WarehouseID AND
UserDept.DepartmentID = Pointer.DepartmentID
JOIN Department ON Pointer.DepartmentID = Department.DepartmentID
JOIN Report ON Pointer.ReportID = Report.ReportID
WHERE
User.UserID = 'anyUserID'

 

[butler]

Thanks much for all of your help,

I tried the above and got the same 6 records, without the needed non-matching record. One thing that I did notice is that when I do leave off the Pointer.ymd = 20010723, the statement does work - kind of. I get 1908 hits, representing matching ReportID's for the first 2 UserFavorite records for all available dates. I also get the third UserFavorite record returned 3 times instead on one. Once for each match in the UserWarehouse table (The test user has 3 matching records in the UserWarehouse table).

I do feel I am getting closer!!

 

[butler]

OK, I got this simplified version to work:

SELECT
UserFavorite.ReportID,
Pointer.ymd,
Pointer.Fname
FROM
UserFavorite left outer join Pointer
on
UserFavorite.ReportID = Pointer.ReportID and
Pointer.ymd = 20010626 and
Pointer.DepartmentID = "Sales" and
(Pointer.WarehouseID = 10 or
Pointer.WarehouseID = 11 or
Pointer.WarehouseID = 12)
WHERE
UserFavorite.UserID = 'anyUserID'

Now how do I add additional table relationships without 'breaking' it?? (Example: Pointer.DepartmentID = Department.DepartmentID instead of Pointer.DepartmentID = "Sales&quot

Thanks,
--bill

 

[tlbroadbent]

I would add each of the other tables, one at a time and test to make sure the correct result is returned.

One note of caution. Your ON clause syntax is non-standard. Unfortunately, SQL Server accepts and processes the query. Some day, in a future version, SQL Server may become more ANSI compliant and the query will fail. All of the criteria that is not part of the JOIN relationship belongs in the WHERE clause rather than the ON clause. I suggest the following change. The query should return the same result and syntax is proper.

SELECT
UserFavorite.ReportID,
Pointer.ymd,
Pointer.Fname
FROM
UserFavorite left outer join Pointer
on
UserFavorite.ReportID = Pointer.ReportID
WHERE
UserFavorite.UserID = 'anyUserID' and
Pointer.ymd = 20010626 and
Pointer.DepartmentID = "Sales" and
(Pointer.WarehouseID = 10 or
Pointer.WarehouseID = 11 or
Pointer.WarehouseID = 12)

The highlighted lines do not define the relationship between the two tables.

BTW: Is YMD defined as integer? If so, why not datetime? Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[butler]

Thanks for the heads-up. I will make the changes.

'ymd' is defined as an 8 digit number to be compatible with existing COBOL applications and databases. It just makes thing easer!

Any suggestions on adding one of the other tables? I tried adding the Department table (Pointer.Department = Department.DepartmentID where DepartmentID is equal to "Sales&quot but 'broke' the query...

 

[tlbroadbent]

I finally caught on! Sorry to not see this sooner. Let's step back to the original query or my version of it.

SELECT
User.UserID,
UserFavorite.ReportID,
UserWare.WarehouseID,
UserDept.DepartmentID,
Pointer.ymd,
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)
Left Join (Pointer
Inner Join Department
On Pointer.DepartmentID=Department.DepartmentID
Inner Join Report Pointer.ReportID=Report.ReportID)
On UserWare.WarehouseID = Pointer.WarehouseID
And UserDept.DepartmentID = Pointer.DepartmentID
And UserFavorite.ReportID = Pointer.ReportID

WHERE
User.UserID = 'anyUserID' AND
(Pointer.ymd = 20010723 Or Pointer.ymd Is Null)

You have to test for NULL value when doing an OUTER JOIN. If you liit the slection to htose with that date, you'' never get the non-matching records. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[butler]

Success!! That was it!

The down side is that adding the null check caused the query to go less than a second to about 5 seconds. Is there any way to avoid this slowdown??

I am also getting extra copies of the non-matching record - based on:

extra copies = Non-matching UserFavorites *
matching UserDept records *
matching UserWare records

 

[tlbroadbent]

You might try putting an Index on the Pointer.ymd column if one doesn't exist. You may see significant performance gains, especially if it can be a clustered index. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tlbroadbent]

I meant to add this to the last post.

You can add Distinct to the query to return only rows with Distinct values.

SELECT Distinct
User.UserID, ...

This will probably also slow the query. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin