CODEWITHSUNDEEP
sqlsql-serverdatabasejoin
kev670
kev670

Reputation: 880

Table join comparing and creating a new column using that compare

I have 2 tables... table1 and table2.

I want to display all serial numbers from table1

table2 has also has serial numbers in it

I would like to compare the serial numbers in table1 with table2

I would then like to display all the serial numbers in table1 and have a second column with a yes if the serial number was in table1 or a no if it wasn't

Is this possible to do with a sql statement or do I have to build a seperate table? i'm running sql-server.

Upvotes: 0

Views: 464

Answers (3)

GarethD
GarethD

Reputation: 69789

If serial numbers in each table are unique then you could use:

SELECT  Table1.SerialNumber,
        CASE WHEN Table2.SerialNumber IS NULL THEN 'No' ELSE 'Yes' END AS [IsInTable2]
FROM    Table1.SerialNumber
        LEFT JOIN Table2
            ON Table2.SerialNumber = Table1.SerialNumber

If there are Duplicates in one or both tables then either of the following will work:

SELECT  DISTINCT 
        Table1.SerialNumber,
        COALESCE([IsInTable2], 'No') [IsInTable2]
FROM    Table1.SerialNumber
        OUTER APPLY
        (   SELECT  TOP 1 'Yes' [IsInTable2]
            FROM    Table2
            WHERE   Table2.SerialNumber = Table1.SerialNumber
        ) Table2


SELECT  DISTINCT
        Table1.SerialNumber,
        CASE WHEN Table2.SerialNumber IS NULL THEN 'No' ELSE 'Yes' END [IsInTable2]
FROM    Table1.SerialNumber
        LEFT JOIN 
        (   SELECT  DISTINCT SerialNumber
            FROM    Table2
        ) Table2
            ON Table2.SerialNumber = Table1.SerialNumber

Upvotes: 2

Badmiral
Badmiral

Reputation: 1589

Try this 

SELECT t1.serialnumber as serialnumber, Case 
WHEN t1.serialnumber = t2.serialnumber then    'YES' else 'NO' END
FROM table1 t1
LEFT JOIN table2 t2 with (nolock) on t1.serialnumber = t2.serialnumber;

Hopefully that should work

Upvotes: 1

ean5533
ean5533

Reputation: 8994

If we assume that the serial numbers are unique in each table then you can do an outer join. Using a LEFT OUTER JOIN will grab you all rows from the left side and optionally grab you any matching rows on the right side. Then you can do a comparison to see if a matching row was found in table2.

SELECT t1.serial, CASE WHEN t2.serial IS NULL THEN 'No' ELSE 'Yes' END
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.serial = t2.serial;

Upvotes: 1

Related Questions