CODEWITHSUNDEEP
phpmysqlsql
Richard
Richard

Reputation: 14625

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

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'.

Upvotes: 1

Views: 192

Answers (4)

user359040
user359040

Reputation:

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

Upvotes: 2

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

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

Upvotes: 1

Lion
Lion

Reputation: 19027

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

Upvotes: 3

Muthu Krishnan
Muthu Krishnan

Reputation: 1658

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

Upvotes: -1

Related Questions