Advanced Search

Results 1 to 6 of 6

Thread: Select in Where

  1. #1
    Join Date
    Apr 2007
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Select in Where

    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:

    Code:
    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?
    Code:
    SELECT [...] FROM profiles WHERE (SELECT userid FROM users WHERE username=theusername)

  2. #2
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    Code:
    SELECT userid FROM profiles WHERE userid=SUBSTRING((SELECT userid FROM users WHERE username=username))
    Ryan
    Sevierville, TN

  3. #3
    Join Date
    Apr 2007
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Code:
    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)

  4. #4
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

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

    Code:
    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
    Ryan
    Sevierville, TN

  5. #5
    Join Date
    Apr 2007
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Thanks a lot!!!

    It worked perfectly

    /phb5000

  6. #6
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •