Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Unanswered: inner join ignores where clause

    Using Pervasive SQL v8, this query ignores the WHERE clause. If I remove the INNER JOIN, the query works correctly. How do I do this query with the INNER JOIN?

    Query = Select arlinh.documentnumber, arlinh.locationkey, arlinh.revenueacctkey, arlinh.shipdate, inloc.itemclasskey from arlinh INNER JOIN inloc ON (arlinh.itemkey = inloc.itemkey); WHERE ((arlinh.shipdate BETWEEN '2007-01-01' AND '2007-01-08') AND (arlinh.revenueacctkey BETWEEN '40000000' AND '4999Z999'))

    With the INNER JOIN, all the records in the database are in the resulting recordset; while, without it, only the records defined by the WHERE clause are in the resulting recordset.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    A couple of questions:
    1. where are you running the query?
    2. Is "Select arlinh.documentnumber, arlinh.locationkey, arlinh.revenueacctkey, arlinh.shipdate, inloc.itemclasskey from arlinh INNER JOIN inloc ON (arlinh.itemkey = inloc.itemkey); WHERE ((arlinh.shipdate BETWEEN '2007-01-01' AND '2007-01-08') AND (arlinh.revenueacctkey BETWEEN '40000000' AND '4999Z999'))" the exact query?

    If number 2 is the correct query, what happens if you remove the semicolon (";") from the query so it looks like:
    Select arlinh.documentnumber, arlinh.locationkey, arlinh.revenueacctkey, arlinh.shipdate, inloc.itemclasskey from arlinh INNER JOIN inloc ON (arlinh.itemkey = inloc.itemkey) WHERE ((arlinh.shipdate BETWEEN '2007-01-01' AND '2007-01-08') AND (arlinh.revenueacctkey BETWEEN '40000000' AND '4999Z999'))
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Feb 2007
    Posts
    6

    inner join ignores where clause

    1. I am running the the query in VB6-ADO
    2. Yes, that is the exact query

    I removed the semicolon and the query worked as it was suppossed to. I changed the WHERE dates and again, it worked correctly. I added some more fields and again, it worked correctly. Thanks.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    No worries. The semicolon is a statement separator in PSQL.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Feb 2007
    Posts
    6
    Does that mean that I could (or should) have placed a semicolon at the end of the complete sql query?

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    You could but it's not required unless you've got multiple statements.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Feb 2007
    Posts
    6
    That's good to know. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •