At 02:04 PM 12-06-02 +0200, you wrote:

> >btw, the syntax for the EXISTS() predicate is
> >
> >SELECT U.URUN_KUTUK_ID
> >, U.CARIID
> >, U.MARKAID
> >, U.URUN_KOD
> >, U.URUN_AD_TR
> >, U.URUN_AD_EN
> >, U.GTIP
> >FROM URUN_KUTUK U
> >WHERE (EXISTS (
> > SELECT I.GM_PART_NUMBER FROM INVOICE_DETAIL I
> > WHERE I.INVOICE_MASTER_ID=437
> > and I.GM_PART_NUMBER = U.URUN_KOD
> >))
> >
> >However, EXISTS() won't work for you here. It will select either ALL of
> >the rows (if there is a row in the table matching the subselect) or NONE of
> >the rows (if there is no matching row). EXISTS() would help you if this
> >query were in a stored procedure as a FOR SELECT query.
>
>Huh? Helen, are you still having that headache or is there something I just
>cannot see? In my mind, this query returns all rows having a URUN_KOD which
>matches a GM_PART_NUMBER of records in INVOICE_DETAIL where the
>INVOICE_MASTER_ID is 437 - i.e. probably some of the records. Records with
>a different URUN_KOD will not be returned.

No, it selects all rows if there are any invoice_detail rows that match
URUN_KOD, otherwise it selects no rows. EXISTS() returns either true or
false.
As there is no selection criterion other than true or false in the WHERE
clause, logically it is like

if (exists(all that stuff)) then
return all rows
else
don't return any rows

The logic of this is not the same as the correlated subselect:

select...where mine.col1 in (select yours.colx from yours where something
of mine matches something of yours)

nor is the same as the join statement:

select...
from mine join yours
on mine.col1 = yours.colx

The EXISTS() test will only be useful where the true or false result
actually conditions what happens, e.g.

create procedure .....
...
as
declare variable <one for each col selected>
...
begin
for SELECT U.URUN_KUTUK_ID
, U.CARIID
, U.MARKAID
, U.URUN_KOD
, U.URUN_AD_TR
, U.URUN_AD_EN
, U.GTIP
FROM URUN_KUTUK U
into :<variables>
do begin
if (EXISTS (
SELECT I.GM_PART_NUMBER FROM INVOICE_DETAIL I
WHERE I.INVOICE_MASTER_ID=437
and I.GM_PART_NUMBER = :URUN_KOD_variable
)) then
begin
<select that row>;
suspend;
end

>Confused yet again,

Oh no! :-))


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________