Subqueries

piersk

Can you return more than one result in a subquery.

i.e.

select id
from table_1
where id = (select id from table_2 where age < 10)

nemonoman

From the MySQL manual [is the db you are using?]

Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1.

Up to version 4.0, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported. You can, however, use the function IN() in other contexts.

there's more:

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

piersk

yes I am using MySQL, but it doesnt really answer my question. I know that subqueries are possible with later versions of MySQL, but can I retrieve more than one result, for example (using my earlier example) more than one id if there are more than one records with ages within the boundaries set.

nemonoman

I'll just assume that you're using a version of MySQL that doesn't allow subqueries.

What you can do is return this same info by a join:

SELECT table1.id
FROM table1, table2
WHERE table1.id=table2.id
AND table2.age<10

piersk

Ta muchly

Sxooter

have you tried it with in()???

select * from table where id in (select id from other_table)

nemonoman

The IN() construct will not work if the querier is using a version of MySQL that is less than v 4.1: Please see my original answer which points to the MySQL manual:

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1.

Up to version 4.0, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported.

tomhath

Join should give the same results, but be careful of duplicate rows in the result set; you might need a DISTINCT in the select because the join gives a cross product. In my experience, it's usually faster to do a join with a distinct than a subselect.

nemonoman

Sorry, tomhath's right. I always assume the existence of a normalized database; not safe to do in this forum. If your ids aren't unique:

SELECT DISTINCT table1.id
FROM table1, table2
WHERE table1.id=table2.id
AND table2.age<10

SELECT table1.id
FROM table1, table2
WHERE table1.id=table2.id
AND table2.age<10
GROUP BY table1.id