Inner member of Outer Join Clause

7 Jul 2005 9:03 PM

LauraRB

I'm trying to run this query - essentially, an employee may not have a
position, but we still want to see the employye listed - so we use an outer
join to show all employees even if they don't have a position. If they do
have a position, we want to join that position to another table to get the
position description. Here's the query:

select c.*, d.* from
    (select a.emplid, a.name, isnull(b.position_nbr,'0') NBR
    from ps_personal_data a, ps_rptc_ee_posn b
    where a.emplid *= b.emplid) c ,
(select descr, position_nbr from ps_position_data) d
where d.position_nbr = NBR

this returns with this error:

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

Is there any way around this?? We need to run several queries similar to
this, and it makes no sense that SQL won't do this.

7 Jul 2005 9:22 PM

Jeremy Williams

Untested, since there was no DDL to work from:

SELECT
     a.emplid,
     a.name,
     COALESCE(b.position_nbr,'0') AS nbr,
     d.descr,
     d.position_nbr
FROM
     ps_personal_data a LEFT OUTER JOIN
     ps_rptc_ee_posn b ON a.emplid = b.emplid
     LEFT OUTER JOIN ps_position_data d
     ON COALESCE(b.position_nbr,'0') = d.position_nbr

You might want to consider using the SQL-92 standard join syntax instead of
the '*=' syntax that is deprecated my Microsoft.

Show quote

"LauraRB" <Laur***@discussions.microsoft.com> wrote in message
news:B5173590-E9AC-4B3B-9AEC-05DF38CA5E17@microsoft.com...
> I'm trying to run this query - essentially, an employee may not have a
> position, but we still want to see the employye listed - so we use an
outer
> join to show all employees even if they don't have a position. If they do
> have a position, we want to join that position to another table to get the
> position description. Here's the query:
>
> select c.*, d.* from
> (select a.emplid, a.name, isnull(b.position_nbr,'0') NBR
> from ps_personal_data a, ps_rptc_ee_posn b
> where a.emplid *= b.emplid) c ,
> (select descr, position_nbr from ps_position_data) d
> where d.position_nbr = NBR
>
> this returns with this error:
>
> The table 'ps_rptc_ee_posn' is an inner member of an outer-join clause.
This
> is not allowed if the table also participates in a regular join clause.
>
>
> Is there any way around this?? We need to run several queries similar to
> this, and it makes no sense that SQL won't do this.

7 Jul 2005 9:49 PM

--CELKO--

This is one of the reasons not to use the old *= syntax. Tables can
appear only as preserved or unpresered in a single query, not both.
Look for another recent posting of mine on how theLEFT OUTER JOIN works
and why various versions of extended equality stink.