CODEWITHSUNDEEP
sqlsql-serverinner-join
Michael Fletcher
Michael Fletcher

Reputation: 117

SQL joining two tables, null same result

i'm trying to create a report for fields which have been updated, so basically like this:

| X | Y | Z | - Table 1 
| X | Y | P | - Table 2

| NUL | NUL | P | - Is what i want the outcome to be

Does anyone know if this is possible / how to approach it? I'm not very competent with SQL! Cheers,

Upvotes: 0

Views: 70

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93754

use Left Outer Join to get the result

SELECT Table1.col1,
       Table1.col2,
       Table2.col3
FROM   Table2
       LEFT OUTER JOIN Table1
              ON Table2.col3 = Table1.col3

SQL FIDDLE DEMO

Upvotes: 1

CiucaS
CiucaS

Reputation: 2128

HEre is my solution

CREATE TABLE #Temp1( A varchar(10), B varchar(10), C varchar(10) , ID Int )

CREATE TABLE #Temp2( A varchar(10), B varchar(10), C varchar(10) , ID Int )

INSERT INTO #Temp1 VALUES('X','Y','Z',1)

INSERT INTO #Temp2 VALUES('X','Y','P',1)

SELECT 
    CASE #Temp1.A WHEN #Temp2.A THEN NULL ELSE #TEmp2.A END as A,
    CASE #Temp1.B WHEN #Temp2.B THEN NULL ELSE #TEmp2.B END as B,
    CASE #Temp1.C WHEN #Temp2.C THEN NULL ELSE #TEmp2.C END as C
 FROM #Temp1 full outer join #Temp2 on #Temp1.ID = #Temp2.ID

Use a case for each column, if column A from #temp1 matches column b from #Temp2 then it will print null, else it will print #Temp2 column

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you do not know what the fields match, then you have a problem. If you have an id that does the match, then you can use a full outer join. A typical query in this situation would look like:

select id,
       (case when told.id is null then 'NEW'
             when tnew.id is null then 'DELETED'
             else 'UPDATED'
        end) as what,
       (case when told.col1 = tnew.col1 or told.col1 is null and tnew.col1 is null then NULL
             else tnew.col1
        end) as new_col1,
       (case when told.col2 = tnew.col2 or told.col2 is null and tnew.col2 is null then NULL
             else tnew.col2
        end) as new_col2,
       (case when told.col3 = tnew.col3 or told.col3 is null and tnew.col3 is null then NULL
             else tnew.col3
        end) as new_col3
from tableold told full outer join
     tablenew tnew
     on told.id = tnew.id;

This is likely to be what you want, unless your two tables only have one row each.

Upvotes: 1

Pred
Pred

Reputation: 9042

Here is a simple, but type intensive solution:

SELECT
   CASE WHEN (
     T1.Field1 = T2.Field1 OR (T1.Field1 IS NULL AND T2.Field1 IS NULL)
   ) THEN NULL ELSE T2.Field1 END AS Field1,
   CASE WHEN (
     T1.Field2 = T2.Field2 OR (T1.Field2 IS NULL AND T2.Field2 IS NULL)
   ) THEN NULL ELSE T2.Field1 END AS Field2
/** and so on **/
FROM
  Table1 T1
  FULL OUTER JOIN Table2 T2
    ON T1.JoinField = T2.JoinField

You have to compare each field individually from the two tables. You can use a FULL OUTER JOIN to get all record from each tables

Also you have to check if the value in each table is NULL (T1.Field1 IS NULL AND T2.Field2 IS NULL). Remember NULL is never equals to any value neither NULL.

Note: FULL OUTER JOIN is maybe too broad, so you can use either LEFT/RIGHT OUTER JOIN or INNER JOIN, but you have to chose the join visely to match the business requirements.

Upvotes: 1

Related Questions