Hi

I have a basic order master/detail relationship that I would like to filter
on.  I would like to filter on orders where specific products are included
for instance.

For orders that have product A1 or A2, this is straight forward.

SELECT orderno from orders o

LEFT OUTER JOIN OrdDets d ON (d.orderno = o.orderno)

WHERE (d.prodcode='A1') OR (d.prodcode='A2')



But If I want orders which include both product A1 and A2 and I try



SELECT orderno from orders o

LEFT OUTER JOIN OrdDets d ON (d.orderno = o.orderno)

WHERE (d.prodcode='A1') AND (d.prodcode='A2')



Obviously, prodcode cannot be both A1 and A2 at the same time. Any ideas
what the sql should be.

Thanks in advance.

Paul Harrison

Off the top of my head...

SELECT orderno from orders o

LEFT OUTER JOIN OrdDets d ON (d.orderno = o.orderno)

WHERE (d.prodcode='A1') AND (d.orderno in (select OD.orderno from OrdDets
OD where (OD.prodcode='A2')))

If all you need to obtain is "orderno", why use the Orders Table do:

SELECT d.orderno from OrdDets d
WHERE (d.prodcode='A1') AND (d.orderno in (select OD.orderno from OrdDets
OD where (OD.prodcode='A2')))

Walter

wrote in message
news:683F16CA-D546-44D7-A9D1-576FA3966F93@news.elevatesoft.com...
> Hi
>
> I have a basic order master/detail relationship that I would like to
> filter on.  I would like to filter on orders where specific products are
> included for instance.
>
> For orders that have product A1 or A2, this is straight forward.
>
> SELECT orderno from orders o
>
> LEFT OUTER JOIN OrdDets d ON (d.orderno = o.orderno)
>
> WHERE (d.prodcode='A1') OR (d.prodcode='A2')
>
>
>
> But If I want orders which include both product A1 and A2 and I try
>
>
>
> SELECT orderno from orders o
>
> LEFT OUTER JOIN OrdDets d ON (d.orderno = o.orderno)
>
> WHERE (d.prodcode='A1') AND (d.prodcode='A2')
>
>
>
> Obviously, prodcode cannot be both A1 and A2 at the same time. Any ideas
> what the sql should be.
>
> Thanks in advance.
>
> Paul Harrison
>
>

<< I have a basic order master/detail relationship that I would like to
filter on.  I would like to filter on orders where specific products are
included for instance. >>

Is this a DBISAM or ElevateDB question ?  This is the ElevateDB SQL
newsgroup, and ElevateDB hasn't been released yet so we require that all EDB
questions be posted in the elevatedb.beta newsgroup.

--
Tim Young
Elevate Software
www.elevatesoft.com