How do you store result row count in nested SELECT?

Question

Hi,

I have a MySQL query where I have a nested SELECT that returns an array to the parent:

SELECT ...
FROM ...
WHERE ... IN (SELECT .... etc)

I would like to store the number of returned results (row count) from the nested SELECT, but doing something like IN (SELECT count(...), columnA) does not work, as the IN expects just one result.

Is there a way to store the returned result count for later use within the parent statement?

Answer 1

You're probably going to have to select the results of your nested statement into a temporary table. Then you can do an IN and a count on it later. I'm more familiar with MS-SQL, but I think you should be able to do it like this:

CREATE TEMPORARY TABLE tmp_table AS
SELECT something
FROM your_table;

SELECT ...
FROM ...
WHERE ... IN (SELECT * FROM tmp_table);

SELECT count(*) FROM tmp_table;

If that doesn't work, you may have to provide full details to the temporary table creation statement as you would with a normal "CREATE TABLE". See here in the MySQL manual, and here for a similar example.

CREATE TEMPORARY TABLE tmp_table
(
    tableid INT,
    somedata VARCHAR(50)
);

INSERT INTO tmp_table
SELECT ...
FROM ...

SELECT ...
FROM ...
WHERE ... IN (SELECT * FROM tmp_table);

SELECT count(*) FROM tmp_table;

Rich

Answer 2

You mentioned in your comment that your query look like this:

SELECT
  tabA.colA,
  tabA.colB
FROM tabA
WHERE tabA.colA IN ( SELECT tabA.colA FROM tabA WHERE tabA.colB = 1 )

I might be missing something, but you don't need a subquery for this. Why don't you do it in a regular where condition:

SELECT
  tabA.colA,
  tabA.colB,
FROM tabA
WHERE tabA.colB = 1

Answer 3

You can use IN predicate for multiple columns like this:

SELECT  *
FROM    table
WHERE   (col1, col2) IN
        (
        SELECT  col3, col4
        FROM    othertable
        )

If you want to select COUNT(*) along with each value, use this:

SELECT  colA, colB, cnt
FROM    (
        SELECT  COUNT(*) AS cnt
        FROM    tabA
        WHERE   colB = 1
        ) q,
        tabA
WHERE   colB = 1