SQL Statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: SQL Statement

  1. #1
    Tim Manos Guest

    SQL Statement

    Hello all,

    I'm trying to retrieve an ADO Recordset form an Access database and I'm
    using the following SQL statement:

    SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001# )
    WHERE First LIKE 'A%'

    which is obviously wrong. How can I modify this statement to get the
    disirable result?

    Thanks in advance

    Tim



  2. #2
    Colin Moore Guest

    Re: SQL Statement


    Hi Tim,

    Instead of this:

    SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
    )
    WHERE First LIKE 'A%'

    try this:

    SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
    ) AND
    First LIKE 'A%'

    Your problem was that you had two where clauses, you can only have one where
    clause but it can have many conditions

    Hope that helps

    Colin Moore
    IS Dept.
    Ext 188




    "Tim Manos" <timmanos@t-online.de> wrote:
    >Hello all,
    >
    >I'm trying to retrieve an ADO Recordset form an Access database and I'm
    >using the following SQL statement:
    >
    >SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#

    )
    >WHERE First LIKE 'A%'
    >
    >which is obviously wrong. How can I modify this statement to get the
    >disirable result?
    >
    >Thanks in advance
    >
    >Tim
    >
    >



  3. #3
    Arthur Wood Guest

    Re: SQL Statement


    Tim,
    Why do think thet the SQL is "obviously wrong"?

    It looks fine to me, as the Outer WHERE applies to the Main part of the Query,
    and the inner where only applies to the SubQuery.

    Of course, you could simplify the whole thin as:

    SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#

    AND First LIKE 'A%')

    which would then apply the filter on First in the inner query, and only return
    the names that you appear to require.

    Arthur Wood


    "Tim Manos" <timmanos@t-online.de> wrote:
    >Hello all,
    >
    >I'm trying to retrieve an ADO Recordset form an Access database and I'm
    >using the following SQL statement:
    >
    >SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#

    )
    >WHERE First LIKE 'A%'
    >
    >which is obviously wrong. How can I modify this statement to get the
    >disirable result?
    >
    >Thanks in advance
    >
    >Tim
    >
    >



  4. #4
    Colin Moore Guest

    Re: SQL Statement


    Sorry Tim I didn't see the inner query

    Colin
    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Tim,
    > Why do think thet the SQL is "obviously wrong"?
    >
    >It looks fine to me, as the Outer WHERE applies to the Main part of the

    Query,
    >and the inner where only applies to the SubQuery.
    >
    >Of course, you could simplify the whole thin as:
    >
    >SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    >>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#

    >AND First LIKE 'A%')
    >
    >which would then apply the filter on First in the inner query, and only

    return
    >the names that you appear to require.
    >
    >Arthur Wood
    >
    >
    >"Tim Manos" <timmanos@t-online.de> wrote:
    >>Hello all,
    >>
    >>I'm trying to retrieve an ADO Recordset form an Access database and I'm
    >>using the following SQL statement:
    >>
    >>SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    >>FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#

    >)
    >>WHERE First LIKE 'A%'
    >>
    >>which is obviously wrong. How can I modify this statement to get the
    >>disirable result?
    >>
    >>Thanks in advance
    >>
    >>Tim
    >>
    >>

    >



  5. #5
    Rohit Wason Guest

    Re: SQL Statement


    >SELECT * FROM ( SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#

    )
    >WHERE First LIKE 'A%'



    Tim,
    It is not clear from your post what is the 'desirable' result. But you can
    actually combine the two WHERE clauses to get the result expected from the
    query you have written. So even this:

    SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    FROM CustomerAddresses
    WHERE TimeStamp BETWEEN #7/10/2001# AND
    #7/13/2001# AND
    First LIKE 'A%'

    will work in the same manner, unless you want some different results.
    Cheers,
    Rohit

  6. #6
    Sue Harsevoort Guest

    Re: SQL Statement

    If you combined the both where statements in the inner query could you just
    eleminate the outer query all together and just have:

    SELECT First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND #7/13/2001#
    AND First LIKE 'A%'

    since as far as I can tell the outer query is just selecting all the records
    from then inner query.

    Sue

    "Arthur Wood" <wooda@saic-trsc.com> wrote in message
    news:3b4f10b5$1@news.devx.com...
    >
    > Tim,
    > Why do think thet the SQL is "obviously wrong"?
    >
    > It looks fine to me, as the Outer WHERE applies to the Main part of the

    Query,
    > and the inner where only applies to the SubQuery.
    >
    > Of course, you could simplify the whole thin as:
    >
    > SELECT * FROM ( SELECT

    First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    > >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND

    #7/13/2001#
    > AND First LIKE 'A%')
    >
    > which would then apply the filter on First in the inner query, and only

    return
    > the names that you appear to require.
    >
    > Arthur Wood
    >
    >
    > "Tim Manos" <timmanos@t-online.de> wrote:
    > >Hello all,
    > >
    > >I'm trying to retrieve an ADO Recordset form an Access database and I'm
    > >using the following SQL statement:
    > >
    > >SELECT * FROM ( SELECT

    First,Last,Street,ZIP,Town,TimeStamp,Modified,UserID
    > >FROM CustomerAddresses WHERE TimeStamp BETWEEN #7/10/2001# AND

    #7/13/2001#
    > )
    > >WHERE First LIKE 'A%'
    > >
    > >which is obviously wrong. How can I modify this statement to get the
    > >disirable result?
    > >
    > >Thanks in advance
    > >
    > >Tim
    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center