none
full outer join Issue

    Question

  • Hi

    I need to Perform Full outer join on email basis

    email  key1   Email  key2

    abc     1        abc    33

    jj      22           

                        lll       67

    I need data based on email

    Like ,

    email  key1    key1

    abc     1           33

    jj        22

    lll                     67

    how can i do this?

    Kindly help

    Friday, June 28, 2013 8:28 AM

Answers

  • you can try something like this:

    DECLARE @Table1 TABLE
    (
    	EMail	VARCHAR(50),
    	Key1	INT
    )
    
    DECLARE @Table2 TABLE
    (
    	EMail	VARCHAR(50),
    	Key2	INT
    )
    
    INSERT INTO @Table1 VALUES('abc', 1)
    INSERT INTO @Table1 VALUES('jj', 22)
    
    INSERT INTO @Table2 VALUES('abc', 33)
    INSERT INTO @Table2 VALUES('lll', 67)
    
    
    SELECT	COALESCE(T1.EMail, T2.EMail), T1.Key1, T2.Key2
    FROM	@Table1 T1
    FULL JOIN	@Table2 T2
    			ON T1.Email = T2.Email

    Also, Please post your table structure.


    - Vishal

    SqlAndMe.com

    Friday, June 28, 2013 8:35 AM

All replies

  • Hi

    I need to Perform Full outer join on email basis

    email  key1   Email  key2

    abc     1        abc    33

    jj      22           

                        lll       67

    I need data based on email

    Like ,

    email  key1    key1

    abc     1           33

    jj        22

    lll                     67

    how can i do this?

    Kindly help

    Please share Table structure with some sample data.

    Thanks.


    bala krishna

    Friday, June 28, 2013 8:33 AM
  • I have done using

    ISnull

    (email,TK_Email)Email

    Friday, June 28, 2013 8:34 AM
  • you can try something like this:

    DECLARE @Table1 TABLE
    (
    	EMail	VARCHAR(50),
    	Key1	INT
    )
    
    DECLARE @Table2 TABLE
    (
    	EMail	VARCHAR(50),
    	Key2	INT
    )
    
    INSERT INTO @Table1 VALUES('abc', 1)
    INSERT INTO @Table1 VALUES('jj', 22)
    
    INSERT INTO @Table2 VALUES('abc', 33)
    INSERT INTO @Table2 VALUES('lll', 67)
    
    
    SELECT	COALESCE(T1.EMail, T2.EMail), T1.Key1, T2.Key2
    FROM	@Table1 T1
    FULL JOIN	@Table2 T2
    			ON T1.Email = T2.Email

    Also, Please post your table structure.


    - Vishal

    SqlAndMe.com

    Friday, June 28, 2013 8:35 AM
  • try like this one below,

    DECLARE @table TABLE
      (
         email1 VARCHAR(50),
         key1   INT,
         Email2 VARCHAR(50),
         key2   INT
      )
    
    INSERT INTO @table
    SELECT 'abc', 1, 'abc', 33
    UNION 
    SELECT 'jj', 22, NULL,NULL
    union
    SELECT NULL,NULL,'lll', 67
    
    
    select t.email1,key1,key2 from (
    SELECT email1,key1,key2
    FROM   @table 
    union
    SELECT email2,key1,key2
    FROM   @table ) t WHERE t.email1 IS NOT NULL


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Friday, June 28, 2013 8:47 AM