hi dmitry

thanks it works.., I try this statement

select <:your_param>, ID, NAME, QTY
from PRODUCT left join PODETAIL on PRODUCT_ID = ID and PO_ID =
<:your_param>


but further question, if the PRODUCT table has STATUS field,
and we want the list of all product only with STATUS = 1,
how to make the query ?

I mean something like this :

PRODUCT :
P1 - Product1 - 1
P2 - Product2 - 1
P3 - Product3 - 1
P4 - Product4 - 0
P5 - Product5 - 0

PODETAIL
1 - P1 - 10
1 - P2 - 10

the result should be

1 - P1 - Product1 - 10
1 - P2 - Product2 - 10
1 - P3 - Product3 - <null>

so we filter the list of product again...., it show not all products but
certain products that meet criteria (STATUS = 1).

how to make query like that ?

rgds
herman



-----Original Message-----
From: Dmitry Yemanov [mailto:dimitr_ex@...]
Sent: Tuesday, September 04, 2001 10:37 PM
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] left outer join in interbase


Hi Herman,

> [snip]
>
> I want to make output that will return the PO_ID, PRODUCT_ID,
> PRODUCT_NAME,
> QTY
> of PODETAIL with ID = 1.
> but it should return all products .., so it will be like this :
>
> 1 - P1 - Product1 - 10
> 1 - P2 - Product2 - 20
> 1 - P3 - Product3 - <null>
> 1 - P4 - Prodcut4 - <null>
> 1 - P5 - Product5 - <null>
>
>
> how can I make query such that ?
> The PODETAIL.PO_ID will be sent as parameter.
>
> please note that the biggest problem is there is a WHERE
> parameter that
> limited the PODETAIL.PO_ID.

Try this:

select <:your_param>, ID, NAME, QTY
from PRODUCT left join PODETAIL on PRODUCT_ID = ID and PO_ID =
<:your_param>

or play with something like this to get more exotic (and much slower)
solution:

select distinct z.PO_ID, x.ID, x.NAME, y.QTY
from PRODUCT x left join PODETAIL y on y.PRODUCT_ID = x.ID and y.PO_ID =
<:your_param>
left join PODETAIL z on z.PO_ID = <:your_param>

HTH,
Dmitry



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/



[Non-text portions of this message have been removed]