Re: SQL query

gcb45_at_dial.pipex.com a écrit :

> I am looking for some assistance with a SQL query.
> I have a table within my application called USERICONS, containing the
> following fields:
> usercode sequenceno bitmapint trancode description
> st
>
> I have another table called USERROLES, containing the following
> fields:
> usercode rolecode
> st ST
> I have a user called 'st' which has 19 icons. I would like to be able
> to give all users with the rolecode ST the same icons that 'st' has.
> This is where I hit a problem.
>
> If there was one user I would use the following SQL:
> insert into usericons (usercode, sequenceno, bitmapint, trancode,
> description)
> (select '&usercode', sequenceno, bitmapint, trancode, description
> from USERICONS
> where usercode = 'st');
>
> So, if I entered a user named 'jh1' when prompted by '&usercode', all
> of 'st' data will be inserted into the USERICONS table but with the
> new username.
> BUT, how can I do this for a list of users. My query:
> select usercode from USERICONS
> where rolecode = 'ST';
> gives me the list of users, I just don't know how to incorporate this
> into the above query.
>
> Any help would be appreciated, I'm sure there is some simple answer
> somewhere :)
>
> thanks
> Gary

You have to use an auto-join:

INSERT INTO usericons(usercode, sequenceno, bitmap, trancode, description)

(SELECT B.usercode, A.sequenceno, A.bitmap, A.trancode, A.description  FROM Usericons A, Usericons B
 WHERE A.usercode = 'st' AND B.usercode != 'st' AND

                B.usercode in (SELECT usercode
                                       FROM Userroles
                                       WHERE rolecode = 'ST')

Perhaps there is a better solution. I must say that I didn't try it but I think that you have the idea. Received on Tue Apr 20 1999 - 11:30:06 CDT