sybase restrictions— inner member of outer join, for regular join

refer to Sybase Book – Chapter 4: Joins: Retrieving Data from Several Tables
for this
Outer join restrictions

If a table is an inner member of an outer join, it cannot participate in both an outer join clause and a regular join clause. The following query fails because the salesdetail table is part of both the outer join and a regular join clause:

select distinct sales.stor_id, stor_name, title
from sales, stores, titles, salesdetail
where qty > 500
and salesdetail.title_id =* titles.title_id
and sales.stor_id = salesdetail.stor_id
and sales.stor_id = stores.stor_id

Msg 303, Level 16, State 1:
Server ’FUSSY’, Line 1:
The table ’salesdetail’ is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

If you want to know the name of the store that sold more than 500 copies of a book, you must use a second query. If you submit a query with an outer join and a qualification on a column from the inner table of the outer join, the results may not be what you expect. The qualification in the query does not restrict the number of rows returned, but rather affects which rows contain the null value. For rows that do not meet the qualification, a null value appears in the inner table’s columns of those rows.

as for me, I am coding,

--and t.account_id *= ca.client_account_id
--and t.fund_account_id *= f.fund_account_id
    and (t.account_id = ca.client_account_id or t.fund_account_id = f.fund_account_id)

it then throw me above error, as ca, and f are inner member of the outer join, at the same time, trying to do another regular join.

comment the first two condition, as in the code, solved the problem.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s