0

Any ideas as to why this query doesn't work on mariadb? It seems to follow the pattern on the mariadb documentation site

SELECT ...
(INTERSECT | EXCEPT | UNION [ALL | DISTINCT]) SELECT ...

( SELECT t1.visitid, t1. patientid, t1.visitdate
FROM db.medi_officevisit t1
WHERE t1.visitdate = (SELECT t2.visitdate
                 FROM db.medi_officevisit t2
                 WHERE t2.PatientID = t1.PatientID and VISITdate >= '2018-01-01'
                 ORDER BY t2.visitID DESC
                 LIMIT 1) )
EXCEPT
( SELECT t1.visitid, t1. patientid, t1.visitdate
FROM db.medi_officevisit t1
WHERE t1.visitdate = (SELECT t2.visitdate
                 FROM db.medi_officevisit t2
                 WHERE t2.PatientID = t1.PatientID and VISITdate >= '2017-01-01' and VISITDATE <= '2018-01-01'  
                 ORDER BY t2.visitID DESC
                 LIMIT 1) );

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXCEPT ( SELECT t1.visitid, t1. patientid, t1.visitdate FROM db.medi_o' at line 8

vee
  • 680
  • 1
  • 10
  • 27
  • 1
    What version of MariaDB do you have? `EXCEPT` was added in 10.3.0. See https://mariadb.com/kb/en/library/except/ – Barmar Aug 14 '18 at 03:50
  • 10.2.16-MariaDB-10.2.16+maria~xenial mariadb.org binary distribution so what should i use instead? – vee Aug 14 '18 at 03:52
  • 1
    See https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to return all rows in one table or subquery that don't exist in another table or subquery. – Barmar Aug 14 '18 at 03:54
  • 1
    Or update to a newer version of MariaDB. – Barmar Aug 14 '18 at 03:55
  • rip a sad day indeed – vee Aug 14 '18 at 03:57
  • @barmar would like to post the answer? – vee Aug 14 '18 at 04:03
  • @ValerioZhang . . . You should ask another question. Even if the query worked it would not do what you want. It would return all rows in the first subquery, because the visit dates would be different. I would strongly recommend that you ask another question and explain what you want to do, provide sample data, and desired results. – Gordon Linoff Aug 14 '18 at 11:41

1 Answers1

0

EXCEPT was added in MariaDB version 10.3.0. You need to upgrade your version of MariaDB to be able to do this.

If you can't, see the solutions in Return row only if value doesn't exist

Barmar
  • 741,623
  • 53
  • 500
  • 612