Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Question Unanswered: all the ids in temp2 should have same client and drug (was "Need Sql Help - Urgent")

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

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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

    Code:
    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

Posting Permissions

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