7

Is it possible for a subquery to return two values onto the outer query? Such as:

SELECT 1, 
       (SELECT COUNT(*), MAX(*) FROM test_table WHERE test=123) 
FROM another_table

Or is there a better way to do this?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • If Another_Table has 20 rows, you'd get 20 rows of output containing the same data, unless the column `test` is present only in Another_Table and not in Test_Table (in which case, it is a funny way of writing the restriction (which is a fairly extreme example of 'meiosis'). – Jonathan Leffler Dec 20 '10 at 01:08

4 Answers4

8

If you use the subquery in the FROM clause rather than the field list, then you can treat the output as a table and refer to the separate columns.

Orbling
  • 20,413
  • 3
  • 53
  • 64
  • I'm a bit confused by that. Can you explain a bit further, please? –  Dec 20 '10 at 00:42
  • 2
    `SELECT A.a, A.b FROM (SELECT a, b FROM MyTable) AS A;` – siride Dec 20 '10 at 00:48
  • @kevin Your subquery, can go as an additional table in the `FROM` clause with an alias (eg. `AS test_t`, name the fields in the subquery too), and refer to the values in the field list: `SELECT 1, test_t.count, test_t.max FROM (SELECT ...` – Orbling Dec 20 '10 at 00:49
  • Only issue here is that without join criteria, this suggestion will produce a cartesian product. – OMG Ponies Dec 20 '10 at 01:03
  • @OMG Ponies: Quite right, it needs restricting. If the queries are disjoint, then it might be better as two subqueries in the field list. OP only asked if it was "possible for a subquery to return two values onto the outer query" - answer, yes. Suitability was not accounted for. – Orbling Dec 20 '10 at 01:09
1

You are just selecting numbers as results so couldn't you just do:

SELECT 1, COUNT(*), MAX(*) FROM test_table WHERE test=123
zsalzbank
  • 9,685
  • 1
  • 26
  • 39
0

Not possible

mysql> select 1, (select 2, 3) from dual;
ERROR 1241 (21000): Operand should contain 1 column(s)

If you are dealing with two tables and you what the results in one line, you should preform a join.

Miguel Silva
  • 633
  • 5
  • 12
0

Hmm, it depends on what exactly you want to do with the data, you can join two tables using JOIN syntax, and one of the tables can actually be a subquery. I think that is probably what you want.

I'm not even user what your current query will do..

Documentation: http://dev.mysql.com/doc/refman/5.0/en/join.html

Aatch
  • 1,846
  • 10
  • 19