Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

select will no work with AND operator

2 views
Skip to first unread message

Larry

unread,
Nov 5, 2012, 7:23:11 PM11/5/12
to
Hi, this one works great:

SELECT SDATE, SPOT, STRIKE, SETTLEMENT
FROM `OPT_FTSEMIB`
WHERE TYPE = "P"
AND STRIKE >=13500
AND STRIKE <=15000
AND EXPIRY = "MAR13"
AND SDATE = "2012-11-02"
LIMIT 0 , 30

while thw following will not:

SELECT SDATE, SPOT, STRIKE, SETTLEMENT
FROM `OPT_FTSEMIB`
WHERE TYPE = "P"
AND STRIKE =13500
AND STRIKE = 14000
AND STRIKE= 14500
AND STRIKE= 15000
AND EXPIRY = "MAR13"
AND SDATE = "2012-11-02"
LIMIT 0 , 30

what's wrong with that query?

thanks

Jerry Stuckle

unread,
Nov 5, 2012, 7:37:14 PM11/5/12
to
How can STRIKE equal 13500, 14000, 14500 and 15000? It is only one value.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

Doug Miller

unread,
Nov 5, 2012, 8:02:44 PM11/5/12
to
"Larry" <dontme...@got.it> wrote in news:5098586c$0$17945
$4faf...@reader1.news.tin.it:

>
> SELECT SDATE, SPOT, STRIKE, SETTLEMENT
> FROM `OPT_FTSEMIB`
> WHERE TYPE = "P"
> AND STRIKE =13500
> AND STRIKE = 14000
> AND STRIKE= 14500
> AND STRIKE= 15000
> AND EXPIRY = "MAR13"
> AND SDATE = "2012-11-02"
> LIMIT 0 , 30
>
> what's wrong with that query?

There cannot possibly be any rows that satisfy the condition
STRIKE = 13500 AND STRIKE = 14000.

Think about it.

You probably want
WHERE type = "P"
AND (strike = 13500 OR strike = 14000 OR strike = 14500 OR strike = 15000)
AND expiry = "MAR13"
...

Geoff Muldoon

unread,
Nov 5, 2012, 9:13:54 PM11/5/12
to
doug_at_mil...@example.com says...

> > SELECT SDATE, SPOT, STRIKE, SETTLEMENT
> > FROM `OPT_FTSEMIB`
> > WHERE TYPE = "P"
> > AND STRIKE =13500
> > AND STRIKE = 14000
> > AND STRIKE= 14500
> > AND STRIKE= 15000
> > AND EXPIRY = "MAR13"
> > AND SDATE = "2012-11-02"
> > LIMIT 0 , 30
> >
> > what's wrong with that query?
>
> There cannot possibly be any rows that satisfy the condition
> STRIKE = 13500 AND STRIKE = 14000.
>
> Think about it.
>
> You probably want
> WHERE type = "P"
> AND (strike = 13500 OR strike = 14000 OR strike = 14500 OR strike = 15000)
> AND expiry = "MAR13"
> ...

Or:
AND STRIKE IN (13500, 14000, 14500,15000)

GM

Doug Miller

unread,
Nov 5, 2012, 10:40:42 PM11/5/12
to
Geoff Muldoon <geoff....@trap.gmail.com> wrote in
news:MPG.2b031d685...@news.albasani.net:
Well, yes, but the OP clearly doesn't understand the difference between AND and OR, else
he would not have been puzzled by the failure of his query -- thus I judged he'd learn more
from an example using the two together, than from an example using IN.
0 new messages