Re: Sql question -- MIDRANGE-L

OK here's my contribution to the growing list:

select cust# from transfile where item# = '12a' and item# <> '13a' group by
cust#

I just tried it on something here. (Performance comparison?)

--Alan




----- Original Message -----
From: "Alexei Pytel" <pytel@us.ibm.com>
To: <midrange-l@midrange.com>
Sent: Saturday, May 18, 2002 2:04 PM
Subject: Re: Sql question


>
> "Not equal" predicate often does not perform well, so I would slightly
> refine it:
>
> select distinct cust# from cust where item# = '12a' and cust# NOT in
> (select cust# from
> transfile where item# = '13a')
>
> In fact, it is very close to my proposal (from points of view of
semantics,
> implementation and performance).
>
>     Alexei Pytel
>
>
>
>
>                       "Paul Raulerson"
>                       <praulerson@hot.rr        To:
<midrange-l@midrange.com>
>                       .com>                     cc:
>                       Sent by:                  Subject:  Re: Sql question
>                       midrange-l-admin@m
>                       idrange.com
>
>
>                       05/17/2002 11:25
>                       PM
>                       Please respond to
>                       midrange-l
>
>
>
>
>
> I did not understand the request - but the answer is still almost as
> simple. Simply
> do a subselect of the customers who meet the second criteria.
>
> select * from cust where item# = '12a' and cust# in (select cust# from
> transfile where
>   item# <> '13a')
>
> I don't have a system up to run that query on, so the syntax is probably
> not 100% correct,
> but it is close enough that a minute or two with the manual will give you
> the correct syntax.
> Be sure to do an explain on the thing though. There are actually at least
> two ways to do that,
> and probably more like three or four.
>
>
> -Paul
>
>
> ----- Original Message -----
> From: "Vernon Hamberg" <vhamberg@attbi.com>
> To: <midrange-l@midrange.com>
> Sent: Friday, May 17, 2002 11:13 PM
> Subject: Re: Sql question
>
>
> > This'll give both 123 and 469 - he wants only 469
> >
> > At 08:58 PM 5/17/02 -0500, you wrote:
> > >SELECT CUST#, ITEM# from TRANSFILE WHERE ITEM# = '12a'
> > >
> > >
> > >----- Original Message -----
> > >From: "Smith, Mike" <Mike_Smith@RGCResources.com>
> > >To: <midrange_L@midrange.com>
> > >Sent: Friday, May 17, 2002 12:32 PM
> > >Subject: Sql question
> > >
> > >
> > > > This message is in MIME format. Since your mail reader does not
> understand
> > > > this format, some or all of this message may not be legible.
> > > > --
> > > > [ Picked text/plain from multipart/alternative ]
> > > > I have a file that has data like this
> > > >
> > > >  <file:Transfile> file:Transfile
> > > > Cust#           Item#
> > > > 123             '12a'
> > > > 123             '13a'
> > > > 469             '12a'
> > > > 550             '13a'
> > > >
> > > > I want to be able to select customers who have ordered item '12a'
but
> > > > not '13a'
> > > >
> > > > So I would end up with
> > > >
> > > > 123 as the only record returned out of this data set.
> > > >
> > > > Can I do this with a SQL statement?
> > > >
> > > > Thanks
> > > >
> > > > Mike
> > > >
> > > > _______________________________________________
> > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> > > > To post a message email: MIDRANGE-L@midrange.com
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > > > or email: MIDRANGE-L-request@midrange.com
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/midrange-l.
> > > >
> > > >
> > >
> > >
> > >_______________________________________________
> > >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > >To post a message email: MIDRANGE-L@midrange.com
> > >To subscribe, unsubscribe, or change list options,
> > >visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > >or email: MIDRANGE-L-request@midrange.com
> > >Before posting, please take a moment to review the archives
> > >at http://archive.midrange.com/midrange-l.
> >
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > or email: MIDRANGE-L-request@midrange.com
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>