[Teddy]

Howdy folks, I have a pesky little logic error that seems to be eluding me that I believe is related to a join precedence error. I have a fairly complex schema to work with, but for examples sake I'll just say three tables.

ag : ag_num, ag_fname, ag_lname
qt : qt_num, ag_num
pl : pl_num, qt_num

Now I want to retrieve all of the records from ag and return null for qt.* and pl.*. For some reason this is eluding me, my train of thought is as follows:

SELECT ag.*, qt.*, pl.*
FROM ag LEFT OUTER JOIN qt ON ag.ag_num=qt.ag_num
INNER JOIN pl.qt_num=qt.qt_num

I know that the error is with the INNER JOIN between pl and qt, but I cannot figure out how to properly define that relationship. For some reason it's just not clicking for me.

The last time I ran into this issue was with returning a single record based on criteria in the where clause. Essentially the record was returning nothing, as opposed to null, because I was only filtering a dataset. I discovered that if you filter the dataset instead of defining it in the join statement, you will not return null. I solved that by moving the criteria into the join statement thereby redefining the base dataset.

That wont work for this particular situation. I need to be able to use qt as an intermediary to pl without having it exclude records. What's the basic hook that I'm missing here?

[r123456]

If you will be using the reults returned from the set of QT and PL then a view would be convenient.

select *
from ag LEFT OUTER JOIN
(select qt.ag_num from qt INNER JOIN pt ON qt.qt_num = pl.qt_num) IJ
ON ag.ag_num = IJ.ag_num

I have only selected qt.ag_num from the result set given by QT joined on PL, as I am not sure of what else you wanted to display. Note however that you cannot use select *, as both qt and pl contain duplicate colum headers, if you need to display all columns from qt, pl then you can rename the duplicates.

By doing the OUTER JOIN followed by an INNER JOIN without brackets to specify precedence you are essentially just doing an inner join. The outer join returns a set with non matching tuples included in the set with Null values to represent the other table's values. This set is then compared with the third table using a standard inner join, as a result the tuples with nulls will be disregarded hence an inner join between table1, table2 and table3.

[Teddy]

Thanks for the reply!

Unfortunately, I receive a syntax error when attempting your method. I am using SQL Server 2k, I received "ERROR NEAR INNER JOIN". However, your reply gave me a good tip to go on that allowed me to solve it myself! I am most appreciative.

I figured out that the error in my logic was I assumed the join statement was executed left to right as far as precedence is concerned. However, you pointed out that for this scenario, the join really needs to be on the end of the statement. by rephrasing my query to:

SELECT ag_num
FROM pl INNER JOIN qt ON pl.qt_num=qt.qt_num
RIGHT OUTER JOIN ag ON ag.ag_num=qt.ag_num

I have essentially defined the bracketing you mentioned. As I needed to first define the inner join between pl and qt, and THEN compare it to ag.

... it's always the simple stuff that gets ya.