locked
Is this SQL query possible RRS feed

  • Question

  • Hi,

    is it possible to retrieve records outside the criteria range

    i have a table with 3 fields (name,date,occurs) see sample

    (c)name               (d)date            (n)occurs                  recno()

    Bill                       01/01/13          1                              1

    Bill                       01/10/13          1                              2

    James                  01/01/13          1                              3

    James                  01/02/13          2                              4

    Tom                     01/04/13          1                              5

    Sally                    01/02/13          1                               6

    Sally                    01/03/13          2                               7

    Sally                    01/10/13          1                               8

    i want to retrieve name,date,occurs where date = 01/01/13 and occurs = 1

    and all records where date is greater than or = to m.mydate and occurs > 1

    records to retrieve are 1,3 and 4

    thanks in advance

    newbie to foxpro

    Max

    Saturday, May 25, 2013 1:55 AM

Answers

  • SELECT NAME,DATE,OCCURS FROM YOURTABLE ;

    WHERE (DATE = DATE(2013,1,1) AND OCCURS = 1) OR ;

    (DATE>=M.MYDATE AND OCCURS > 1) ORDER BY OCCURS, DATE


    • Proposed as answer by Pavel Celba Saturday, May 25, 2013 11:49 AM
    • Marked as answer by Mike Feng Thursday, May 30, 2013 12:36 PM
    Saturday, May 25, 2013 11:19 AM
    Answerer
  • How the rec no. 3 can meet your criteria? "occurs > 1" does not evaluate to True.

    You should start to study SQL probably. It is nothing difficult. You may start e.g. here: http://www.sqlcourse.com/index.html or here: http://www.w3schools.com/sql/.

    You should distinguish between AND, OR, and NOT operators and their exact meaning in the WHERE clause.  JOIN part of SQL can wait at the moment.

    FoxPro allows to test various SQL commands and their modifications in Command window so you may see the result immediately.

    It is much better than to say "this does not work" and post the modified question. If you understand the answer then you should be able to modify it. If you don't understand the answer then you have to study SQL once more.

    BTW, the answer is "No record meets your modified criteria" because you are requesting occurs to be 1 and greater than 1 at the same time which is not possible in today's programming.

    • Marked as answer by Mike Feng Thursday, May 30, 2013 12:36 PM
    Saturday, May 25, 2013 10:37 PM

All replies

  • SELECT

    cname,cdate,occurs FROM test WHERE (cdate = CTOD('01/01/2013') AND occurs = 1 ) or (cdate >= CTOD('01/01/2013') AND occurs > 1)

    Lakshmi

    Saturday, May 25, 2013 3:52 AM
  • SELECT NAME,DATE,OCCURS FROM YOURTABLE ;

    WHERE (DATE = DATE(2013,1,1) AND OCCURS = 1) OR ;

    (DATE>=M.MYDATE AND OCCURS > 1) ORDER BY OCCURS, DATE


    • Proposed as answer by Pavel Celba Saturday, May 25, 2013 11:49 AM
    • Marked as answer by Mike Feng Thursday, May 30, 2013 12:36 PM
    Saturday, May 25, 2013 11:19 AM
    Answerer
  • these SQL query will not work because they return more records then i need

    i need name,date,occurs WHERE date = 01/01/13 and occurs = 1

    thats returns records 1 and 3

    then i need to retrieve records where name = name, date = date + 4, occurs > 1

    record #3 meets this criteria

    Any help will be greatly appreciated

    thanks in advance

    newbie to foxpro

    Max

    Saturday, May 25, 2013 8:22 PM
  • How the rec no. 3 can meet your criteria? "occurs > 1" does not evaluate to True.

    You should start to study SQL probably. It is nothing difficult. You may start e.g. here: http://www.sqlcourse.com/index.html or here: http://www.w3schools.com/sql/.

    You should distinguish between AND, OR, and NOT operators and their exact meaning in the WHERE clause.  JOIN part of SQL can wait at the moment.

    FoxPro allows to test various SQL commands and their modifications in Command window so you may see the result immediately.

    It is much better than to say "this does not work" and post the modified question. If you understand the answer then you should be able to modify it. If you don't understand the answer then you have to study SQL once more.

    BTW, the answer is "No record meets your modified criteria" because you are requesting occurs to be 1 and greater than 1 at the same time which is not possible in today's programming.

    • Marked as answer by Mike Feng Thursday, May 30, 2013 12:36 PM
    Saturday, May 25, 2013 10:37 PM