CODEWITHSUNDEEP
mysqlsql
Mauro Valvano
Mauro Valvano

Reputation: 933

MySQL IN() with subquery stopped working?

I have a view called "v_documents" where a i have a field "document_type_name" that is based on some fields. However the field is a string with a name.

Now i want all documents where the type name is contained in another table. But...

This work:

SELECT * FROM v_documents WHERE document_type_name IN ('PREVENTIVO', 'FATTURA');

This not:

SELECT * FROM v_documents WHERE document_type_name IN (
     SELECT type FROM t_types
);

Where t_types contains a list of document types and nothing more. It give me 0 records.

But if i use = istead of IN() and i return only one record from the subquery it works.

The problem is that if i'm not wrong this code worked before. I don't know what is happening.

P.S The t_types table DON'T HAVE null values!

EDIT: using the subquery in a field seems work. Why in the IN() not?

Here a screen: on the left the subquery used as field, on the right the return records from SELECT type FROM t_types

enter image description here

EDIT 2: Screen for @MatBailie's answere. But i used a LEFT JOIN instead of LEFT OUTER JOIN because i get a MySQL error.

enter image description here

Upvotes: 1

Views: 57

Answers (3)

MatBailie
MatBailie

Reputation: 86765

This is only half an answer, but too long for a comment.

I would begin by using the following query to directly compare what is in each table...

SELECT
  *
FROM
(
  SELECT
    CONCAT('[', document_type_name, ']') AS document_type_name
  FROM
    v_documents
  GROUP BY
    document_type_name
)
  AS documents
FULL OUTER JOIN
(
  SELECT
    CONCAT('[', type, ']') AS type
  FROM
    t_types
  GROUP BY
    type
)
  AS types
    ON types.type = documents.document_type_name
ORDER BY
  COALESCE(document_type_name, type)

This will show every type that exists in both tables, with what matches and what doesn't. The concatenation of the '[' and ']' will help spot leading/trailing spaces.

I'd love to see the results in your question.


EDIT :

And you are certain that the following does not work? (Exactly as is, with no other changes or additions?)

SELECT
  *
FROM
  v_documents
WHERE
  document_type_name IN (SELECT type FROM t_types)

If so, I can't explain it. The existence of any matches from the first query "proves" (or so I thought) that the IN (SELECT) version should be fine.

That said, here are some alternatives.

SELECT
  *
FROM
  v_documents
WHERE
  EXISTS (SELECT * FROM t_types WHERE t_types.type = v_documents.document_type_name)

Or...

SELECT
  v_documents.*
FROM
  v_documents
INNER JOIN
  t_types
    ON t_types.type = v_documents.document_type_name

If there are duplicates in t_types, then you need to use this instead...

SELECT
  v_documents.*
FROM
  v_documents
INNER JOIN
(
  SELECT type FROM t_types GROUP BY type
)
  AS t_types
    ON t_types.type = v_documents.document_type_name

As a side benefit, as the number of types in t_types increases, each of these alternatives will often out perform use of IN (SELECT) any way.


EDIT 2 :

This shouldn't make any difference that I'm aware of, but what happens if you try this?

SELECT
  *
FROM
  v_documents
WHERE
  CONCAT('[', document_type_name, ']')
  IN
  (SELECT DISTINCT CONCAT('[', type, ']') FROM t_types)

Upvotes: 1

Ilesh Patel
Ilesh Patel

Reputation: 2155

Try this:

  SELECT * FROM v_documents WHERE UPPER(trim(document_type_name)) IN
    ( SELECT UPPER(trim(TYPE)) FROM t_types WHERE type IS NOT NULL);

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT *
FROM v_documents
WHERE trim(document_type_name) IN
    ( SELECT trim(TYPE)
     FROM t_types);

Upvotes: 0

Related Questions