CODEWITHSUNDEEP
mysqlsql
BernaMariano
BernaMariano

Reputation: 866

Two select statements in one query

I need to get the users age by his ID. Easy. The problem is, at the first time I don't know their IDs, the only thing I know is that it is in a specific table, let's name it "second".

SELECT `age` FROM `users` WHERE `userid`=(SELECT `id` FROM `second`)

How can I do that?

Upvotes: 3

Views: 12115

Answers (5)

veblock
veblock

Reputation: 1924

SELECT age FROM users WHERE userid IN (SELECT id FROM second)

but preferably

SELECT u.age FROM users u INNER JOIN second s ON u.userid = s.id

Upvotes: 2

Chui Tey
Chui Tey

Reputation: 5554

Your example 

SELECT `age` FROM `users` WHERE `userid`=
  (SELECT `id` FROM `second`  
   WHERE `second`.`name` = 'Berna')

should have worked as long as you add a where criteria. This is called subqueries, and is supported in MySQL 5. Reference http://dev.mysql.com/doc/refman/5.1/en/comparisons-using-subqueries.html

Upvotes: 3

Jordan
Jordan

Reputation: 2758

SELECT age FROM users inner join Second on users.UserID = second.ID

An inner join will be more efficient than a sub-select

Upvotes: 2

Elliot B.
Elliot B.

Reputation: 17651

You want to use the 'in' statement:

select * from a
    where x=8 and y=1 and z in (
        select z from b where x=8 and active > '2010-01-07 00:00:00' group by z
    )

Upvotes: 1

Luke101
Luke101

Reputation: 65238

SELECT age FROM users WHERE userid IN (SELECT id FROM second)

This should work

Upvotes: 6

Related Questions