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

    Question Unanswered: Temp table SQL question

    I have a temp table. that has 3 fields. It gets created according to the criteria of similar id1.

    id1------ id2 ------drug
    1-------- 23-------- A
    1-------- 23-------- B
    1-------- 34-------- A
    1-------- 35-------- C
    1-------- 35-------- A
    . . .
    . . .


    my task is to make all id2s have same drugs in the actual table. e.g: if 23 has drug B in it then
    34 and 35 should also have B in it. if 35 has C in it then 23, 34 should also have C in it.

    In actual table result should be :


    id1----id2 ----drug
    1------23------A
    1------23------ B
    1------ 23------ C
    1------ 34------ A
    1------ 34------ B
    1------ 34------ C
    1------ 35------ C
    1------ 35------ A
    1------ 35------ B
    .--------. -------.


    how can I do that in SQL.

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    Code:
    SELECT IDTable.id2, DrugTable.drug
    FROM (SELECT DISTINCT id2 FROM TempTable) AS IDTable
    CROSS JOIN (SELECT DISTINCT drug FROM TempTable) AS DrugTable

Posting Permissions

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