all the ids in temp2 should have same client and drug (was "Need Sql Help - Urge

I have following tables

Temp1

ID Client Drug
3889 aa A
4998 cc A
4998 dd B
8011 ff C
8011 gg A

and Temp2

ID Client Drug
8011 ff C
8011 gg A

I have a conditional loop which changes the values of these 2 tables. I populate temp2 by getting all the similar ids from temp1. e.g: first time 3889 then 4998 then 8011.

my task is, all the ids in temp2 where temp2.id<>temp1.id should have same client and drug. if 8011 has client bb and drug B then 4998,3889 should also have client bb and drug B in them.

if 4998 has client dd and drug B then 8011, 3889 should also have client dd and drug B and so on. means all the records in temp1 should contain similar clients and drugs in them in actual table. I'll need to add the missing client and drug for the ID where its misiing in actual table.

After this process my actual drug table will look like:


ID Client Drug

3889 dd B
3889 cc A
3889 aa A
3889 ff C
3889 gg A
4998 dd B
4998 cc A
4998 aa A
4998 ff C
4998 gg A
8011 dd B
8011 cc A
8011 aa A
8011 ff C
8011 gg A

How can I achive this goal. Please help...it is not very clear from your post what exactly u want to do. however, looking at the final output i think this sql might start the ball rolling for u

select T1.Id,T2.Client,T2.Drug from Temp1 as T1 join Temp1 as T2 on T1.id <> T2.id
union
select * from Temp1
order by T1.Id