Save selected in variable and then use that variable for next select

Question

I have multiple tables in my database. Let's say the table users looks like this:

Users:
|id|name|gender|access|id_ext|
|1 |  a |  m   |   1  |  32  | 
|3 |  b |  m   |   3  |  33  | 
|4 |  c |  m   |   1  |  34  | 
|5 |  d |  f   |   1  |  35  |

I would like to select the user with for example id_ext = 32 and then run another select statement using that selected users fields.

I can solve this by first getting the user with a query and then create another query with users info, but there must be a way to do this in the same query?

This is the query i use now:

SELECT * FROM users NATURAL JOIN
(SELECT id FROM ages WHERE age BETWEEN
(SELECT limit_age_l FROM users WHERE id=17)
AND (SELECT limit_age_h FROM users WHERE id=17)) as a
WHERE NOT id = 17
AND locale = 'en_US'
AND limit_gender = 1
AND visible = 0
AND NOT EXISTS (SELECT view_id FROM matches WHERE user_id = 17 AND view_id = a.id)
LIMIT 1

Problem is that the values id=17, limit_gender=1 and locale = 'en_US' in the query are not known. These are taken from the user with id_ext = '32'.

What's that another table? If you want a ***resultset*** from multiple tables, you need to use *join*. Please, be more precise. — Lion, Dec 29 '11 at 12:13
@Richard: So to clarify: you want users with the same locale, limit_gender and visible values as the specified user, but with a different id, and where there are no corresponding matches records for the specified user_id? — , Dec 30 '11 at 14:55

score 3 · Answer 1 · answered Dec 29 '11 at 12:08

3

SELECT * FROM Users WHERE id in (SELECT id FROM Users WHERE id_ext='32');

answered Dec 29 '11 at 12:08

Lion

18,729
22
80
110

Noting the outer query can be performed on "the other table". – david Dec 29 '11 at 12:44

score 2 · Accepted Answer · 2011-12-30T15:04:33.540

Yes - assuming your subsequnt query is of the form:

select field1, field2, ...
from Table1
join Table2 on ...
where ...
and Table1.id = N /* previously selected id from users */

Then either by using the first query as a subquery:

select field1, field2, ...
from Table1
join Table2 on ...
where ...
and Table1.id = (select id from users where id_ext ='32')
/* replace = with IN if more than one id will be returned */

Or by joining to the results of the first query as part of the subsequent query:

select field1, field2, ...
from users 
join Table1 on Table1.id = users.id 
join Table2 on ...
where ...
and users.id_ext ='32'

(Note that both of these forms assume that users is not already being joined in the existing query - if it is, just add the users.id_ext ='32' condition to the existing query.)

EDIT: If I have understood the requirements correctly, the required query could be written as:

SELECT u.* 
FROM users u
join ages a on u.id = a.id and 
               u.age between limit_age_l and limit_age_h
join users ul on ul.id = 17 and 
                 ul.id <> u.id and 
                 ul.locale = u.locale and
                 ul.limit_gender = u.limit_gender and
                 ul.visible = u.visible
AND NOT EXISTS (SELECT NULL 
                FROM matches m
                WHERE m.user_id = ul.user_id AND m.view_id = a.id)
LIMIT 1

My query looks like this: START OF QUERY::: SELECT * FROM users NATURAL JOIN (SELECT id FROM ages WHERE age BETWEEN (SELECT limit_age_l FROM users WHERE id=17) AND (SELECT limit_age_h FROM users WHERE id=17)) as a WHERE NOT id = 17 AND locale = 'en_US' AND limit_gender = 1 AND visible = 0 AND NOT EXISTS (SELECT view_id FROM matches WHERE user_id = 17 AND view_id = a.id) LIMIT 1 :::END OF QUERY Where ´id=17´ and ´limit_gender = 1´ and ´locale´ is collected from ´id_ext = 32´ How can i combine the join query you suggested with this. =) — Richard, Dec 30 '11 at 11:19
Why not just add `and users.id_ext ='32'` to your WHERE clause? — , Dec 30 '11 at 11:25
Well it's not that simple because i don't know the `limit_gender`, `locale`, and `id` values from "the beginning", those values are supposed to be collected from the user with `users.id_ext = '32'` — Richard, Dec 30 '11 at 11:35
Why not rephrase your question so that it asks the question you actually want answered? — , Dec 30 '11 at 11:47

score 1 · Answer 3 · edited Dec 29 '11 at 15:59

1

SELECT * FROM users WHERE id = (SELECT id FROM users WHERE id_ext = '32');

edited Dec 29 '11 at 15:59

cambraca

27,014
16
68
99

answered Dec 29 '11 at 12:02

Sudhir Bastakoti

99,167
15
158
162

score -1 · Answer 4 · answered Dec 29 '11 at 12:05

-1

Select * from users as user inner join userinfo as usinfo on usinfo.id=user.id_ext where user.id_ext='32'

answered Dec 29 '11 at 12:05

Muthu Krishnan

1,664
2
10
15

Yes - it will return all columns from all tables in the main query. – Dec 29 '11 at 12:29
Why use join when you have sub-query as option? It will be really expensive on large tables. – Uday Sawant Dec 29 '11 at 14:00
@Uday Sawant: For your reference about the joins and sub query [check here](http://stackoverflow.com/questions/2577174/join-vs-subquery) – Muthu Krishnan Dec 29 '11 at 14:22

Save selected in variable and then use that variable for next select

4 Answers4