Re: SQL query

Mohamad SALEH a écrit :

> 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.

Sorry, I missed it completely. The correct one is the following instruction :

INSERT INTO Usericons
(SELECT Userroles.usercode, sequenceno, bitmap, trancode, description  FROM Usericons, Userroles
 WHERE Usericons.usercode = 'st' AND Userroles.usercode != 'st' AND

                Userroles.rolecode = 'ST')