0

I have a legacy MySQL database (3.x) and I´m trying to run a query that returns all rows from a specific column where the values of the returned rows shall NOT exist in another column, from another table.

I´ve searched all similar questions here and googled for a solution, but could not find why I´m failing in executing this query.

The column from the first table (Table programs) is named 'fkfileid', and the column from the table I want to check against (Table imports) is named 'ifkfileid'. My query is supposed to return all rows from 'programs.fkfileid' that don´t have a matching value in 'imports.ifkfileid'.

This is the query:

SELECT fkfileid FROM programs WHERE fkfileid NOT IN (SELECT ifkfileid FROM imports)

I have to use 'NOT IN' instead of 'NOT EXISTS' because it´s a legacy MySQl database. The error I get from the database is the following:

"You have an error in your SQL syntax near 'SELECT ifkfileid FROM imports)' at line 1"

My query is very similar to several examples shown here, in similar posts. I don´t know where I´m doing wrong.

Example: SQL - find records from one table which don't exist in another

Also, I cannot use 'JOIN' statements because this query will be added to a much more complex query where I would not know how to make it work with 'JOIN'.

It´s not my intention to ask an stupid question with little effort, but I´m not being able to figure this one out...

Thanks for any help or insight,

Community
  • 1
  • 1
Daniel Santos
  • 188
  • 2
  • 15
  • Can you post the structure of the tables "imports" and "programs" (printscreen or SQL code)? – Jefrey Sobreira Santos Jan 05 '16 at 15:43
  • From [this](http://downloads.mysql.com/docs/refman-4.1-en.a4.pdf) doc I read: "From MySQL 4.1.0 on, IN() syntax can also be used to write certain types of subqueries." - page 754. This doesn't help the situation but I would try @PaulWhitfields suggestion – Mr. Meeseeks Jan 05 '16 at 15:52
  • Jefrey, here you go: http://screencast.com/t/qq00B8AV9nx http://screencast.com/t/fXtAXkgA – Daniel Santos Jan 05 '16 at 16:05
  • @Meeseeks The problem is that I´m using MySQL 3.x in this legacy database, so IN with subqueries is only supported from 4.1.0 onwards... – Daniel Santos Jan 05 '16 at 16:08

1 Answers1

0

Try breaking the statement down into atomic parts and test them individually against the database directly:

e.g. ensure that "SELECT ifkfileid FROM imports" returns some data and does not cause an error. If that works, plug the whole line in and see the result. Ensure the first part is syntactically correct by using "SELECT fkfileid FROM programs WHERE fkfileid IN (...) and pick a value that you know exists. Then use NOT IN etc etc.

It is likely that there is a simple syntax error. I am unsure if MySQL is white space sensitive but all the NOT IN queries I have seen have the bracket against the IN directive - e.g. NOT IN(... and not, NOT IN (...

Hope that helps.

Paul Whitfield
  • 48
  • 9
  • Hi Paul, Yes "SELECT ifkfileid FROM imports" works as expected... The full query is SELECT SUBSTRING(program,4,5) FROM programs WHERE deleted = 0 AND fkmgroupid IN (41,38,34,36,39,35,32,30) AND fkfileid NOT IN (fkfileid) ORDER BY SUBSTRING(program,4,5), this code passes but return nothing because of the "NOT IN (fkfileid)". I need to find a way to make "NOT IN (fkfileid)" something like "NOT IN (SELECT ifkfileid FROM imports) or find a way to query the "NOT IN" values from another table to compare against... – Daniel Santos Jan 05 '16 at 15:56
  • fkfileid NOT IN (fkfileid) will never return anything - did you try the NOT IN(50) [or whatever the value may be] to ensure you are using NOT IN correctly. The error implies that the syntax error is immediately after 'IN'... – Paul Whitfield Jan 05 '16 at 16:10
  • I know it Paul... I just used this as an example to see what could be passed... Apparently because I´m on MySQL 3.x I cannot use subqueries, only from 4.1 onwards... – Daniel Santos Jan 05 '16 at 16:13
  • I don´t think I´ll be able to get it done the original way using a subquery... and I suck when dealing with JOIN statements... how could I get what I need enhancing this original query to use a LEFT OUTER JOIN: SELECT SUBSTRING(program,4,5) FROM programs WHERE deleted = 0 AND fkmgroupid IN (41,38,34,36,39,35,32,30) ORDER BY SUBSTRING(program,4,5) – Daniel Santos Jan 05 '16 at 16:16
  • It would be something like: SELECT SUBSTRING(program,4,5) FROM programs p LEFT JOIN import i ON p.fkfileid = i.ifkfileid WHERE deleted = 0 AND fkmgroupid IN (41,38,34,36,39,35,32,30) AND i.ifkfileid IS NULL really not sure of the syntax MySQL 3.x supports but that is valid ANSI-92 – Paul Whitfield Jan 05 '16 at 16:39
  • Paul, I don´t know how to mark the answer above as the answer but you nailed it. This is the correct syntax for the alternative solution. Thanks! – Daniel Santos Jan 05 '16 at 16:46