CODEWITHSUNDEEP
sqljoinduplicates
user2561472
user2561472

Reputation: 17

How to remove common fields in two tables

I have two tables , table 1 and table 2.

The fields of table 1 are : book,pen,pencil,bag

The fields of table 2 are : car,van,book,bike,pencil

When I run the query I want the query to ignore the duplicate or common fields and return the other field.

The output should be as follows,

car,van,bike,pen,bag

Upvotes: 1

Views: 2033

Answers (4)

Fuzzy
Fuzzy

Reputation: 3810

    WITH uniontables AS  (
  SELECT    NULL AS car,
            NULL AS van,
            book,
            NULL AS bike,
            pen,
            pencil,
            bag 
  FROM      [Table 1 ]
  UNION 
  SELECT    car,
            van,
            book,
            bike,
            NULL AS pen,
            pencil,
            NULL AS bag 
  FROM [Table 2 ] )

  SELECT DISTINCT * FROM uniontables

Upvotes: 0

The Hill Boy
The Hill Boy

Reputation: 162

try this one:

declare @table1 table (col1 varchar(max))
declare @table2 table (col1 varchar(max))

insert into @table1 values
('book'),('pen'),('pencil'),('bag')

insert into @table2 values ('car'),('van'),('book'),('bike'),('pencil') 

;with cte
as (
select COUNT(1) as total_item, col1 from (
select col1 from @table1
union all 
select col1 from @table2
)a group by col1
)

select  col1 from cte  where total_item = 1

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

Perhaps:

SELECT x.thing FROM
(
    SELECT thing FROM dbo.Table1
    UNION ALL
    SELECT thing FROM dbo.Table2
) X
GROUP BY x.thing
Having Count(*) = 1

Demo

However, this will also remove items that are duplicates in their table which might or might not be desired.

Upvotes: 2

ZaoTaoBao
ZaoTaoBao

Reputation: 2615

Have you tried sth like this:

delete form X
where (car  = 
    Select distinct car
    from X 
    where x);

distinct--> return the differents values.

Upvotes: 0

Related Questions