How to store the number of rows of the result in a nested SELECT?
I have a MySQL query in which I have a nested SELECT that returns an array to the parent object:
SELECT ...
FROM ...
WHERE ... IN (SELECT .... etc)
I would like to keep the number of returned results (number of rows) from the nested SELECT, but doing something like IN (SELECT count (...), columnA) doesn't work, since IN only expects one result.
Is there a way to store the returned result for later use in the parent expression?
You will probably have to fetch the results of your nested statement into a temporary table. Then you can do IN and calculate later. I'm more familiar with MS-SQL, but I think you should 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 need to provide complete information for the temporary table creation statement, just like you would for a regular "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
a source to share
You mentioned in your comment that your request looks like this:
SELECT
tabA.colA,
tabA.colB
FROM tabA
WHERE tabA.colA IN ( SELECT tabA.colA FROM tabA WHERE tabA.colB = 1 )
I may be missing something, but you don't need a subquery for that. Why don't you do it in the usual case when the condition:
SELECT
tabA.colA,
tabA.colB,
FROM tabA
WHERE tabA.colB = 1
a source to share
You can use a predicate IN
for multiple columns, for example:
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
a source to share