Convert NOT IN to OUTER JOIN (mrst)

Hi, I'm pretty new when it comes to programming sql-quaries. But I've
heard that OUTER JOIN is much faster than NOT IN wich result its the
same.
Can anyone tell me how to rewrite this quary in a proper way to get the
result using OUTER JOIN instead?
The task is to display a list in a databased ComboBox Drop down. This
list shall exclude records shown in a underlying Grid.
select ARTTRPTYP.ARTTRPTYPID,
ARTTRPTYP.ARTTRPTYPNAME
from ARTTRPTYP
where ARTTRPTYP.ARTTRPTYPID not in ( select ARTTRPTYP.ARTTRPTYPID
from
DEPTRP,
ARTTRPTYP,
EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
VEHTYP_ARTTRPTYP
where DEPTRP.DEPID =
:DEPARTURE_ID
and ARTTRPTYP.ARTTRPTYPID(+) =
DEPTRP.ARTTRPTYPID
and DEPTRP_LOAD_VW.DEPID(+) =
:DEPARTURE_ID
and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =
DEPTRP.ARTTRPTYPID
and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
)
order by ARTTRPTYP.ARTTRPTYPID
I would be happy for sure if someone helps me with this task!!!
The Best
/Markus
"zekevarg" wrote:

> Hi, I'm pretty new when it comes to programming sql-quaries. But I've
> heard that OUTER JOIN is much faster than NOT IN wich result its the
> same.
> Can anyone tell me how to rewrite this quary in a proper way to get the
> result using OUTER JOIN instead?
> The task is to display a list in a databased ComboBox Drop down. This
> list shall exclude records shown in a underlying Grid.
>
> select ARTTRPTYP.ARTTRPTYPID,
> ARTTRPTYP.ARTTRPTYPNAME
> from ARTTRPTYP
> where ARTTRPTYP.ARTTRPTYPID not in ( select ARTTRPTYP.ARTTRPTYPID
> from
> DEPTRP,
> ARTTRPTYP,
> EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
> VEHTYP_ARTTRPTYP
> where DEPTRP.DEPID =
> :DEPARTURE_ID
> and ARTTRPTYP.ARTTRPTYPID(+) =
> DEPTRP.ARTTRPTYPID
> and DEPTRP_LOAD_VW.DEPID(+) =
> :DEPARTURE_ID
> and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =
> DEPTRP.ARTTRPTYPID
> and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
> and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
> and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> )
> order by ARTTRPTYP.ARTTRPTYPID
> I would be happy for sure if someone helps me with this task!!!
> The Best
> /Markus
>|||Warning, I am lazy, so I did not acurally look at your derived table, but
here is the general idea. You perform an outer join and then filter by the
rows that do not exist by checking for the key (join field) to be null. So:
SELECT ARTTRPTYP.ARTTRPTYPID, ARTTRPTYP.ARTTRPTYPNAME
FROM ARTTRPTYP a LEFT JOIN
( select ARTTRPTYP.ARTTRPTYPID
from DEPTRP, ARTTRPTYP, EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
VEHTYP_ARTTRPTYP
where DEPTRP.DEPID =
:DEPARTURE_ID
and ARTTRPTYP.ARTTRPTYPID(+) =
DEPTRP.ARTTRPTYPID
and DEPTRP_LOAD_VW.DEPID(+) =
:DEPARTURE_ID
and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =
DEPTRP.ARTTRPTYPID
and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
) b ON a.ARTTRPTYPID = b.ARTTRPTYPID
WHERE b.ARTTRPTYPID IS NULL
order by ARTTRPTYP.ARTTRPTYPID
HTH,
John Scragg
"zekevarg" wrote:

> Hi, I'm pretty new when it comes to programming sql-quaries. But I've
> heard that OUTER JOIN is much faster than NOT IN wich result its the
> same.
> Can anyone tell me how to rewrite this quary in a proper way to get the
> result using OUTER JOIN instead?
> The task is to display a list in a databased ComboBox Drop down. This
> list shall exclude records shown in a underlying Grid.
>
> select ARTTRPTYP.ARTTRPTYPID,
> ARTTRPTYP.ARTTRPTYPNAME
> from ARTTRPTYP
> where ARTTRPTYP.ARTTRPTYPID not in ( select ARTTRPTYP.ARTTRPTYPID
> from
> DEPTRP,
> ARTTRPTYP,
> EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
> VEHTYP_ARTTRPTYP
> where DEPTRP.DEPID =
> :DEPARTURE_ID
> and ARTTRPTYP.ARTTRPTYPID(+) =
> DEPTRP.ARTTRPTYPID
> and DEPTRP_LOAD_VW.DEPID(+) =
> :DEPARTURE_ID
> and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =
> DEPTRP.ARTTRPTYPID
> and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
> and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
> and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> )
> order by ARTTRPTYP.ARTTRPTYPID
> I would be happy for sure if someone helps me with this task!!!
> The Best
> /Markus
>|||Yike, I am having posting issues. Sorry for the multiple posts. I will try
this one more time and call it quits :)
SELECT ARTTRPTYP.ARTTRPTYPID, ARTTRPTYP.ARTTRPTYPNAME
FROM ARTTRPTYP a LEFT JOIN ( select ARTTRPTYP.ARTTRPTYPID
from
DEPTRP,
ARTTRPTYP,
EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
VEHTYP_ARTTRPTYP
where DEPTRP.DEPID =
:DEPARTURE_ID
and ARTTRPTYP.ARTTRPTYPID(+) =
DEPTRP.ARTTRPTYPID
and DEPTRP_LOAD_VW.DEPID(+) =
:DEPARTURE_ID
and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =
DEPTRP.ARTTRPTYPID
and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
) b ON a.ARTTRPTYPID = b.ARTTRPTYPID
WHERE b.ARTTRPTYPID IS NULL
order by ARTTRPTYP.ARTTRPTYPID
"zekevarg" wrote:

> Hi, I'm pretty new when it comes to programming sql-quaries. But I've
> heard that OUTER JOIN is much faster than NOT IN wich result its the
> same.
> Can anyone tell me how to rewrite this quary in a proper way to get the
> result using OUTER JOIN instead?
> The task is to display a list in a databased ComboBox Drop down. This
> list shall exclude records shown in a underlying Grid.
>
> select ARTTRPTYP.ARTTRPTYPID,
> ARTTRPTYP.ARTTRPTYPNAME
> from ARTTRPTYP
> where ARTTRPTYP.ARTTRPTYPID not in ( select ARTTRPTYP.ARTTRPTYPID
> from
> DEPTRP,
> ARTTRPTYP,
> EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
> VEHTYP_ARTTRPTYP
> where DEPTRP.DEPID =
> :DEPARTURE_ID
> and ARTTRPTYP.ARTTRPTYPID(+) =
> DEPTRP.ARTTRPTYPID
> and DEPTRP_LOAD_VW.DEPID(+) =
> :DEPARTURE_ID
> and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =
> DEPTRP.ARTTRPTYPID
> and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
> and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
> and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> )
> order by ARTTRPTYP.ARTTRPTYPID
> I would be happy for sure if someone helps me with this task!!!
> The Best
> /Markus
>|||Hi, thanks for taking time trying to solve this. I've tried our quary
but it does'nt work.
error massage says: ORA-00904: "ARTTRPTYP"."ARTTRPTYPID": invalid
identifier
Do you know the answer to this?
SELECT ARTTRPTYP.ARTTRPTYPID,
ARTTRPTYP.ARTTRPTYPNAME
FROM ARTTRPTYP a LEFT JOIN ( select ARTTRPTYP.ARTTRPTYPID
from DEPTRP,
ARTTRPTYP,
DEPTRP_LOAD_VW,
DEPTRP_ORDERS_VW,
VEHTYP_ARTTRPTYP
where DEPTRP.DEPID =:DEPARTURE_ID
and ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and DEPTRP_LOAD_VW.DEPID(+) =:DEPARTURE_ID
and DEPTRP_LOAD_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and DEPTRP_ORDERS_VW.DEPID(+) =:DEPARTURE_ID
and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and VEHTYP_ARTTRPTYP.VEHTYPID(+) =:VEHTYPID
and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID )
b ON a.ARTTRPTYPID = b.ARTTRPTYPID
WHERE b.ARTTRPTYPID IS NULL
order by ARTTRPTYP.ARTTRPTYPID|||Yes, the answer is the alias.
SELECT a.ARTTRPTYPID, a.ARTTRPTYPNAME
When I converted your sample I aliased the tables with (a) for the original
table and (b) for your derived table.
"zekevarg" wrote:

> Hi, thanks for taking time trying to solve this. I've tried our quary
> but it does'nt work.
> error massage says: ORA-00904: "ARTTRPTYP"."ARTTRPTYPID": invalid
> identifier
> Do you know the answer to this?
> SELECT ARTTRPTYP.ARTTRPTYPID,
> ARTTRPTYP.ARTTRPTYPNAME
> FROM ARTTRPTYP a LEFT JOIN ( select ARTTRPTYP.ARTTRPTYPID
> from DEPTRP,
> ARTTRPTYP,
> DEPTRP_LOAD_VW,
> DEPTRP_ORDERS_VW,
> VEHTYP_ARTTRPTYP
> where DEPTRP.DEPID =:DEPARTURE_ID
> and ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> and DEPTRP_LOAD_VW.DEPID(+) =:DEPARTURE_ID
> and DEPTRP_LOAD_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> and DEPTRP_ORDERS_VW.DEPID(+) =:DEPARTURE_ID
> and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
> and VEHTYP_ARTTRPTYP.VEHTYPID(+) =:VEHTYPID
> and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID )
> b ON a.ARTTRPTYPID = b.ARTTRPTYPID
> WHERE b.ARTTRPTYPID IS NULL
> order by ARTTRPTYP.ARTTRPTYPID
>|||I've tried many combinations witout any great success... :(
Please use the quary above and write it the way you think it should be.
Thanks 4 all your help!
/M|||How can I write it the way I think it should be ... I know nothing about you
r
data, these table structures, or what result you are looking for. You asked
how to turn a NOT IN to a OUTER JOIN. I told you how to do that. I will
show this one more.
select a.ARTTRPTYPID, a.ARTTRPTYPNAME
from ARTTRPTYP a
LEFT OUTER JOIN
( select ARTTRPTYP.ARTTRPTYPID from
DEPTRP, ARTTRPTYP, EPTRP_LOAD_VW, DEPTRP_ORDERS_VW,
VEHTYP_ARTTRPTYP
where DEPTRP.DEPID=:DEPARTURE_ID
and ARTTRPTYP.ARTTRPTYPID(+) =DEPTRP.ARTTRPTYPID
and DEPTRP_LOAD_VW.DEPID(+) =:DEPARTURE_ID
and DEPTRP_LOAD_VW.ARTTRPTYPID(+) =DEPTRP.ARTTRPTYPID
and DEPTRP_ORDERS_VW.DEPID(+) = :DEPARTURE_ID
and DEPTRP_ORDERS_VW.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
and VEHTYP_ARTTRPTYP.VEHTYPID(+) = :VEHTYPID
and VEHTYP_ARTTRPTYP.ARTTRPTYPID(+) = DEPTRP.ARTTRPTYPID
) b
ON a.ARTTRPTYPID = b. ARTTRPTYPID
WHERE b.ARTTRPTYPID IS NULL
order by a.ARTTRPTYPID
Best of luck,
John
"zekevarg" wrote:

> I've tried many combinations witout any great success... :(
> Please use the quary above and write it the way you think it should be.
> Thanks 4 all your help!
> /M
>