Select in Where

[phb5000]

Hi,

Is it possible to use a select statement inside the where clause?

I have two tables:
- users
- profiles

Each profile contains a field called userid (which links the profile to the user).

Lets say I want to retrieve the profile of a user using the user's username:

theuserid = SELECT userid FROM users WHERE username=theusername
profile = SELECT [...] FROM profiles WHERE userid=theuserid

How could I combine these two commands into one command (so that I only have to query the database once from my code)?

Is this possible?

SELECT [...] FROM profiles WHERE (SELECT userid FROM users WHERE username=theusername)

[NXArmada]

SELECT userid FROM profiles WHERE userid=SUBSTRING((SELECT userid FROM users WHERE username=username))

[phb5000]

SELECT name_first FROM profiles WHERE userid=SUBSTRING((SELECT userid FROM users WHERE username='username'))

Does not work

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT userid FROM users WHERE username='username'))' at line 1
(0 ms taken)

[NXArmada]

Removing the SUBSTRING() will fix the problem, sorry about that. example below

SELECT userid FROM profiles WHERE userid=(SELECT userid FROM users WHERE username=username)

I tested the above with MySQL Query Browser and no error was given

[phb5000]

Thanks a lot!!!

It worked perfectly

/phb5000

[exipnos]

better ....

 SELECT profiles.* FROM users left join profiles  on users.userid=profiles.theuserid 
where users.username=theusername