Re: [GENERAL] What is the best way to merge two disjoint tables?

[Chansup Byun]

Rodrigo De León wrote:
On 9/7/07, Chansup Byun <[EMAIL PROTECTED]> wrote:
  
Can someone show me an example SQL statement?
    

I suppose you could add a constant, non-overlapping number to add to
the duplicate IDs, say 1000, and then this:

SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
     , COALESCE(CASE
                  WHEN EXISTS(SELECT 1
                                FROM TABLEB
                               WHERE U_ID = T1.U_ID
                                 AND U_USER <> T1.U_USER)
                    THEN T1.U_ID + 1000
                  ELSE T1.U_ID
                END
              , T2.U_ID
               ) AS U_ID
  FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER

will generate a new list of U_USERs and U_IDs.

Good luck.
  
This is cool!
What I wanted is shown below.

One more question: Is there a way to make the  T2.U_ID + 1000  number to be 
incremental from a given number instead of adding 1000?


SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
    , COALESCE(CASE
                 WHEN EXISTS(SELECT 1
                               FROM TABLEB
                              WHERE U_ID = T1.U_ID
                                AND U_USER <> T1.U_USER)
                   THEN T1.U_ID  
                 ELSE T1.U_ID
               END
             , T2.U_ID + 1000
              ) AS U_ID
 FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER



thanks,

- Chansup




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly