1

A working MySQL-query had to be extended due to the inclusion of a new column in the underlying tables. This column is also now relevant for a condition in the query, therefore I tried to adjust the query with the inclusion of the new column in the condition part by using CONCAT, but without luck so far, meaning I do not get any results.

After trying now for quite a while and also looking e.g. here Using mysql concat() in WHERE clause? , I cannot get it to work aka the query does give an empty result.

When I try the individual parts of the query alone, everything works fine...

this worked:

SELECT a, ... FROM tbl1
LEFT JOIN(
    SELECT a, ... FROM tbl2) AS tbl3 using (a)
 WHERE a 
 NOT IN (SELECT a FROM tbl4 where con=...)

this does not:

SELECT a, b , ... FROM tbl1
LEFT JOIN(  
      SELECT a, b, ... FROM tbl2) AS tbl3 using (a)
WHERE CONCAT(a, b)
NOT IN (SELECT CONCAT(a, b) FROM tbl4 where con=...)

Expected query result should be the result of the JOIN, filtered by the NOT IN condition. Instead I get an empty query result.

Ultimate LaForsch
  • 35
  • 7
  • I think the query you presented might be too minimal for much help, but CONCAT is not necessary for that kind of check in MySQL. MySQL supports conditions on tuples like `(a, b) in ((a1, b1), (a2, b2), ....)` – Uueerdo Sep 04 '19 at 16:18
  • 1
    your question is not clear .. add a proper data sample, your actual result and your expected result – ScaisEdge Sep 04 '19 at 16:18
  • 2
    `a` and `b` are ambiguous in `WHERE CONCAT(a, b)`. You should use table aliases as prefix. – Paul Spiegel Sep 04 '19 at 16:22
  • Regarding the comments about not being clear enough: I edited my question and provided now a code comparison. Hope that makes it a bit clearer... – Ultimate LaForsch Sep 04 '19 at 16:31
  • Are there any nulls in a and b of tbl4? – forpas Sep 04 '19 at 16:37
  • @forpas: Yes, that would be case for some data sets – Ultimate LaForsch Sep 04 '19 at 16:39
  • Don't use `WHERE ... NOT IN`, use a `LEFT JOIN` followed by a `NULL` test. See https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 – Barmar Sep 04 '19 at 16:40
  • concat() returns null if any of its arguments is null and comparing against null returns also null. I don't know your data so you have to check for nulls. – forpas Sep 04 '19 at 16:42
  • Never use `NOT IN ()` if the `` can return NULLs. – Paul Spiegel Sep 04 '19 at 16:46
  • @forpas: That was as a matter of fact the problem. I made a copy of tbl4 and deleted all sets with column 'b' values of null...and it worked. If you like, I would accept your answer. Many thx! – Ultimate LaForsch Sep 04 '19 at 16:48
  • @UltimateLaForsch fine if you solved the problem. – forpas Sep 04 '19 at 16:49
  • @Uueerdo: Thank you for the input. I will check this out and give you feedback. – Ultimate LaForsch Sep 04 '19 at 16:50
  • @Barmar: Thank you for the input. I will check this out and give you feedback. – Ultimate LaForsch Sep 04 '19 at 16:50
  • @Paul Spiegel: Also, thank you. Your second post was the problem as forpas told me also. Regarding the ambiguous, I am not sure, if this is applicable here. As far as I know MySQL is not shy to throw an 'ambiguous error'...;-) – Ultimate LaForsch Sep 04 '19 at 16:57
  • Note: [per the MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat) `CONCAT` returns NULL if any of its arguments are NULL. – Bob Jarvis - Слава Україні Sep 04 '19 at 16:57
  • @Ueerdo: Condition on tuples works in general and even if there are nulls in the relevant columns. I have learned that now. Thank you very much – Ultimate LaForsch Sep 04 '19 at 17:09
  • @UltimateLaForsch Well.. compare [this](https://www.db-fiddle.com/f/vx1i1PkLS3JsDqVmGyLV6P/0) and [that](https://www.db-fiddle.com/f/o2uXNDisAYAeL7b8V3DuqR/0). Error fixed using a table prefix for `b`. But IMHO it's a bug, that MySQL doesn't also complain about `a`. It would be fine for an INNER JOIN. But for a LEFT JOIN `tbl3.a` and `tbl1.a` can have different values, because `tbl3.a` can be NULL due to the LEFT JOIN. – Paul Spiegel Sep 04 '19 at 17:11
  • @Barmar: A further alternative to get my query work. Also, thank you very much – Ultimate LaForsch Sep 04 '19 at 17:19

0 Answers0