Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Question Unanswered: How To pass a variable to a subquery

    Oracle 8.1.7

    Newbie question.
    I'm trying to query a database to produce output similiar too:

    Profile name, Profile label, User Name, User Label
    ---------------------------------------------------------
    Admin Administrator Jim Jim
    Admin Administrator Mark Mark
    WinPrf Windows Prof Jim Jim
    WinPrf Windows Prof Moe Moe
    WinPrf Windows Prof Larry Larry
    ...
    and so on.

    Essentially all users 'assigned' for each profile.

    The Profiles and Users are both in the same table. Profiles have a user_role value of 4, Users 0 or 1.
    Another table identifies which users are assigned to which profiles, via there keys, which is the key of the first table (containing the profiles and users).
    The SQL I currently have produces a listing of all users for each profile. Not just the ones assigned.
    Here it is:
    select p.name, p.label, x.name, x.label
    from tabel1 p,
    (select u.name, u.label
    from table2 u
    where u.user_role <> 4
    ) x
    where p.user_role = 4
    order by p.name;

    What I would like to do is add a condition to the subquery 'where' clause to pass the 'key' from the higher (parent) query. For Example:
    ...
    from table2 u
    where u.user_role <> 4
    and u.key = p.key
    ...

    This should give me ONLY the users assigned to the profile.
    However all attempts to do so fail. It (SQL) doesn't understand p.key in the subquery, so I thought I would make it a variable, but how do I do that and would it help?

    Any ideas on how to code the SQL, pass a variable or accomplish this?

    Thanks in advance.
    Jim

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: How To pass a variable to a subquery

    Originally posted by jlmiller78
    Oracle 8.1.7

    Newbie question.
    I'm trying to query a database to produce output similiar too:

    Profile name, Profile label, User Name, User Label
    ---------------------------------------------------------
    Admin Administrator Jim Jim
    Admin Administrator Mark Mark
    WinPrf Windows Prof Jim Jim
    WinPrf Windows Prof Moe Moe
    WinPrf Windows Prof Larry Larry
    ...
    and so on.

    Essentially all users 'assigned' for each profile.

    The Profiles and Users are both in the same table. Profiles have a user_role value of 4, Users 0 or 1.
    Another table identifies which users are assigned to which profiles, via there keys, which is the key of the first table (containing the profiles and users).
    The SQL I currently have produces a listing of all users for each profile. Not just the ones assigned.
    Here it is:
    select p.name, p.label, x.name, x.label
    from tabel1 p,
    (select u.name, u.label
    from table2 u
    where u.user_role <> 4
    ) x
    where p.user_role = 4
    order by p.name;

    What I would like to do is add a condition to the subquery 'where' clause to pass the 'key' from the higher (parent) query. For Example:
    ...
    from table2 u
    where u.user_role <> 4
    and u.key = p.key
    ...

    This should give me ONLY the users assigned to the profile.
    However all attempts to do so fail. It (SQL) doesn't understand p.key in the subquery, so I thought I would make it a variable, but how do I do that and would it help?

    Any ideas on how to code the SQL, pass a variable or accomplish this?

    Thanks in advance.
    Jim
    There is an easier way to do it with a simple join. However, your table definitions are not clear. You say that table2 "identifies which users are assigned to which profiles" but it's not obvious from your example. Can you post your table definitions?

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    Thanks, 'n_i' for responding. Here is the additional information.

    Profile Table
    user_id, name, label, user_role (= 4 for profiles), ... (additional columns)

    The same table is used for Users (user_role <> 4). In my example, this is table1.

    'User to Profile' table (i.e. profiles assigned to users)
    user_id, profile_id

    where user_id is a 'real' user (i.e. user_role <> 4), and profile_id equates to user_id (where user_role = 4). Both fields relate back to the Profile table. In my example, this is table2.

    Here is a relationship diagram:

    Profile Table: user_id, name, label, user_role(4)
    user_id points to profile_id in 'User to Profile' table: user_id, profile_id
    user_id in this table points back to user_id in Profile table, where user_role <> 4

    Again, I am trying to produce a report (list) of users assigned to a specific profile, for each and every profile.

    Hopefully this additional information/description helps you help me.
    Thanks again.
    Jim

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by jlmiller78
    Thanks, 'n_i' for responding. Here is the additional information.

    Profile Table
    user_id, name, label, user_role (= 4 for profiles), ... (additional columns)

    The same table is used for Users (user_role <> 4). In my example, this is table1.

    'User to Profile' table (i.e. profiles assigned to users)
    user_id, profile_id

    where user_id is a 'real' user (i.e. user_role <> 4), and profile_id equates to user_id (where user_role = 4). Both fields relate back to the Profile table. In my example, this is table2.

    Here is a relationship diagram:

    Profile Table: user_id, name, label, user_role(4)
    user_id points to profile_id in 'User to Profile' table: user_id, profile_id
    user_id in this table points back to user_id in Profile table, where user_role <> 4

    Again, I am trying to produce a report (list) of users assigned to a specific profile, for each and every profile.

    Hopefully this additional information/description helps you help me.
    Thanks again.
    Jim
    I think this should do the trick:

    select p.user_id as profile, u.user_id as user
    from table1 p, table2 r, table1 u
    where p.user_role=4 and p.user_id=r.profile_id
    and r.user_id=u.user_id and u.user_role<>4
    order by 1

    You can look at the query like this:
    1) select all profiles from table1 and call it "p"
    2) select all users from table1 and call it "u"
    3) by means of the relationship table ("r") join "p" and "u" together
    4) order the result by profile

  5. #5
    Join Date
    Jan 2004
    Posts
    6

    Thumbs up

    Excellent. It was enough to get me started down the correct path. For some unknown reason, I found this to be more complicated than it should have been (and than I thought it would).

    'u_i' ... Thanks a bunch! Your assistance is greatly appreciated.
    Jim

Posting Permissions

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