-1

I have an app that receives an object and generates a dynamic query for gathering reporting data. That object contains filter properties that eventually translate into "WHERE" statements in MySQL. A simpler and on topic version of the generated query looks something like this:

 SELECT cr3.id AS col_1, 
 count(table1.ids) AS col_2 
 FROM table1 
 LEFT JOIN table2 cr3 ON table1.id = cr3.Id
 WHERE cr3.id NOT IN ('val') GROUP BY 1;

The Queried DB has this dataset (without any filtering applied):

| col_1 | col_2 |
| null  | 100   |
| val   | 100   |

When that's executed the result set is empty. I was expected to get the first row of the dataset:

| null | 100 |

If I adjust there WHERE clause from

WHERE cr3.id NOT IN ('val') => WHERE (cr3.id NOT IN ('val') OR cr3.id IS NULL)

I receive the expected results, but this is dynamically generated for different types of queries and I can't find anyway to tweak the "NOT IN" operator to have a proper behavior and return everything that doesn't match what's specified as an argument.

Any Ideas ?

forpas
  • 160,666
  • 10
  • 38
  • 76
Ovy.Istrate
  • 474
  • 3
  • 15
  • Read the manual about how IN works, in particular how it treats NULL. Also your goal & rewriting & replacing IN are faqs. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. Find out about using EXISTS instead. "Any Ideas ?" is not a valid question. – philipxy Jul 13 '20 at 16:59
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Jul 13 '20 at 16:59
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Jul 13 '20 at 17:02

2 Answers2

1

You can filter table2 before you join:

SELECT t2.id AS col_1, COUNT(t1.id) AS col_2 
FROM table1 t1 
LEFT JOIN (SELECT * FROM table2 WHERE id NOT IN ('val')) AS t2 
ON t2.id = t1.id
GROUP BY t2.id;
forpas
  • 160,666
  • 10
  • 38
  • 76
0

I saw there are many options to obtain the intended result but the solution I was looking for was something that could be easily applied in this dynamic environment and not change the statements that are following the WHERE filtering, in this case the best solution I found was enhancing the filtering part with a COALESCE function call like this:

 SELECT cr3.id AS col_1, 
 count(table1.ids) AS col_2 
 FROM table1 
 LEFT JOIN table2 cr3 ON table1.id = cr3.Id
 WHERE COALESCE(cr3.id, '') NOT IN ('val') GROUP BY 1;
Ovy.Istrate
  • 474
  • 3
  • 15