PDA

View Full Version : Select in Where



phb5000
05-11-2007, 01:14 PM
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
05-11-2007, 02:07 PM
SELECT userid FROM profiles WHERE userid=SUBSTRING((SELECT userid FROM users WHERE username=username))

phb5000
05-11-2007, 02:46 PM
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
05-11-2007, 02:59 PM
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 (http://mysql.org/downloads/gui-tools/5.0.html) and no error was given

phb5000
05-11-2007, 03:14 PM
Thanks a lot!!!

It worked perfectly :D

/phb5000

exipnos
05-17-2007, 01:05 PM
better ....

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