Thread: SQL Query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    14
    Rep Power
    0

    SQL Query


    Hi
    I want to know how to write a correlated subquery for the following task.
    I have two tables:
    Table name : UserTable
    Fields : username varchar2(20)
    userEmailID varchar2(30)

    Table name : DetailsTable
    Fields : DetailsCode number(10)
    username1 varchar2(20) references UserTable(username)
    username2 varchar2(20) references UserTable(username)
    username3 varchar2(20) references UserTable(username)
    username4 varchar2(20) references UserTable(username)
    username5 varchar2(20) references UserTable(username)
    username6 varchar2(20) references UserTable(username)
    username7 varchar2(20) references UserTable(username)
    username8 varchar2(20) references UserTable(username)
    username9 varchar2(20) references UserTable(username)
    username10 varchar2(20) references UserTable(username)



    Now i need emails id list for the following condition

    I want to retrieve the email id's of user where the username is either in username1 or username2 or username3 or username4 or username5 or username6 or username7 or username8 or username9 or username10 where the DetailsCode is matching to 1(say)


    I need to write a correlated subquiery for that..
    one solution is like this but i dont want this solution

    select userEmailID from UserTable
    where username in((select username1 from DetailsTablewhere DetailsCode =1),
    (select username2 from DetailsTable where DetailsCode =1),
    (select username3 from DetailsTable where DetailsCode =1),
    (select username4 from DetailsTable where DetailsCode =1),
    (select username5 from DetailsTable where DetailsCode =1),
    (select username6 from DetailsTable where DetailsCode =1),
    (select username7 from DetailsTable where DetailsCode =1),
    (select username8 from DetailsTable where DetailsCode =1),
    (select username9 from DetailsTable where DetailsCode =1),
    (select username10 from DetailsTable where DetailsCode =1))


    Help me if any one knew the solution
    Thankyou
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    USA
    Posts
    2
    Rep Power
    0
    Try this:
    select userEmailID from UserTable
    where username in (select * from DetailsTable where DetailsCode =1)

IMN logo majestic logo threadwatch logo seochat tools logo