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.