Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
sql help |
Wed, Jan 3 2007 7:34 AM | Permanent Link |
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 | |
Wed, Jan 3 2007 10:03 AM | Permanent Link |
"Walter Matte" | 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 > > |
Wed, Jan 3 2007 2:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |