none
MS Access and SQL Server give different results on the same query on same tables RRS feed

  • Question

  • With two tables

    SELECT patient.patientid, patient.name, q1.untested
    FROM patient LEFT JOIN (SELECT patient.patientid, 'not yet tested' AS untested
    FROM patient LEFT JOIN testresult ON patient.patientid = testresult.fkpatientid
    WHERE testresult.testresultid Is Null) as q1 ON patient.patientid = q1.patientid;

    This seems to be a well known problem. Reported here:
    allenbrowne.com

    stackoverflow

    this forum

    and in many places elsewhere. Will Microsoft fix this?




    • Edited by iotc2000 Friday, October 18, 2019 7:36 AM
    Wednesday, October 16, 2019 12:51 PM

All replies

  • If you read Ken Sheridan's answer you linked to carefully, you would probably conclude that No, this will not be fixed, because it is not wrong. Just a slightly different interpretation of a dark corner of set theory.

    -Tom. Microsoft Access MVP

    Wednesday, October 16, 2019 1:49 PM
  • your syntax is wrong; the query works fine in Access:

    SELECT Patient.patientid, Patient.patientName, testresults.result AS teststatus
    FROM Patient INNER JOIN testresults ON Patient.patientid = testresults.fkpatientID;

    ….note use of patientName rather than Name which is a reserved word.....

    also since when do different brands of databases are expected to have identical syntax?  the sql syntax of Oracle, IBM, MySQL all have variations...    this whole construct is incorrect... just because they are made by the same manufacturer doesn't require 2 entirely different brands of databases to use identical syntax....


    Wednesday, October 16, 2019 2:16 PM
  • Hi Tom, I was first inclined to agree with Ken Sheridan. However, when I tested it in SQL Server, I was forced to disagree. Surely it cannot be right that SQL server gives a different answer. Either SQL Server is correct or MS Access is correct, they cannot both be correct. At the very least they should interpret set theory in the same way.
    • Edited by iotc2000 Wednesday, October 16, 2019 4:37 PM
    Wednesday, October 16, 2019 4:35 PM
  • Point taken on using reserved word name. But the query is correct, its the result that is wrong!
    Wednesday, October 16, 2019 5:15 PM
  • Either SQL Server is correct or MS Access is correct, they cannot both be correct.

    Hi iotc2000,

    When I look at the results, I can imagine that the Access results are different from what one can expect.

    But also the SQL server results are: I would expect Mary to be "pos", and Thabo to be "neg", and surely not both Null.

    Perhaps the query itself could be modified …

    Imb.

    Wednesday, October 16, 2019 7:03 PM
  • Hi lmb. Sure - But the query is just to illustrate the bug. I have edited the query to change the column name from teststatus to untested - hopefully that's clearer.


    • Edited by iotc2000 Thursday, October 17, 2019 7:31 AM
    Thursday, October 17, 2019 5:14 AM
  • hopefully that's clearer.

    Hi iotc2000,

    Yes, it is clear now. I have thought about it, but I have no answer.

    My only conclusion that I probably will not come in such a situation. I hardly make overviews that combine "all" possibilities. I like to make queries that are filtered to the records that need to take "action".

    E.g. which Persons are not yet tested. or which Persons have a result "neg". The queries are simple and unambiguous, but it is a complete different approach.

    Success with your search.

    Imb.

    Thursday, October 17, 2019 9:39 AM
  • Hi lbm

    Thanks for taking the time to look at it. I agree that this approach is probably not best practice. Nevertheless, I am hoping a moderator will come by and say, yes this is a reproducible bug, I'll report it to Microsoft. Can happen sometimes on this site (navigation pane)

    Thursday, October 17, 2019 10:08 AM