4 Replies Latest reply on Jan 9, 2017 9:10 PM by keywords

    Writing SQL query with more than one condition

    keywords

      I've been studiously avoiding getting into SQL hitherto, but now am dipping my toe in the water and finding the going a bit tough for an old bloke. Thanks up front to Beverly for "The-Missing-FM-12-ExecuteSQL-Reference", and assorted other sites I've so far consulted.

      My current hurdle is trying to write a query that has two WHERE parameters. The context is a quoting file with line items for different elements that together make up the quote, and I want to produce sub-totals for some categories. This is what I have so far, and it works for a single parameter:

      Let (

      [

         quote = //<quoteID from current table>

        ; query =

        "

        SELECT SUM ( <number field in line item table> )

        FROM <name of line item table>

        WHERE <category field in line item table> = 'keyword'

        "

        ; result =  ExecuteSQL ( query ; "" ; "" ; "" )

      ] ;

      result

      )

      What I want, however, is to add a second parameter so as to limit the calculation to only those line items from the category that match the current quote record. The above sums all line items for the selected 'keyword' category—but it includes all quotes, not just the current one. From what I have read it seems I should be able to achieve this with an AND line after the WHERE line (I have tried various versions such as AND <foreign key field in line item record> = quote (see first variable above), but so far all to no avail.

      Where am I going wrong?