CODEWITHSUNDEEP
sqlsql-serverdatabase
user3795085
user3795085

Reputation: 31

How make a union of two columns with different value of fields

I Have two columns from different selects in sql server

Table 1

 ID     Name           Bit
....   ............   .....
 1     Enterprise 1   False 
 2     Enterprise 2   True
 3     Enterprise 3   False

Table 2

 ID       Name             Bit 
....    ............     .......
 1      Enterprise 1      True
 2      Enterprise 2      False
 3      Enterprise 3      False

expected result

ID       Name           Bit 
....   ............   ......
1      Enterprise 1    True
2      Enterprise 2    True
3      Enterprise 3    False

the problem is make a union between the two tables and the bit column prevail fields that are true

Any ideas?

Upvotes: 0

Views: 1524

Answers (3)

Ivan
Ivan

Reputation: 4753

SELECT Table1.ID, Table1.Name, IIF(Table1.[Bit]>0 OR Table2.[Bit]>0,1,0) AS [Bit]
FROM 
(VALUES(1,'Enterprise 1',0),(2,'Enterprise 2',1),(3,'Enterprise 3',0)) as Table1(ID,Name,Bit),
(VALUES(1,'Enterprise 1',1),(2,'Enterprise 2',0),(3,'Enterprise 3',0)) as Table2(ID,Name,Bit)
WHERE Table1.ID = Table2.ID

It seems to me that you are just doing a logical OR operation on the Bit column and calling it UNION.

Query Result

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would suggest casting it to an int:

select id, name, cast(max(bitint) as bit) as bit
from ((select id, name, cast(bit as int) as bitint
       from table1
      ) union all
      (select id, name, cast(bit as int) as bitint
       from table2
      )
     ) t12
group by id, name;

With your data, you can also do it using join:

select t1.id, t1.name, (t1.bit | t2.bit) as bit
from table1 t1 join
     table2 t2
     on t1.id = t2.id and t1.name = t2.name;

This assumes all the rows match between the two tables (as in your sample data). You can do something similar with a full outer join if they don't.

Upvotes: 1

Guffa
Guffa

Reputation: 700192

You can make a left join on the other table to exclude the records that should be used from the other table:

select
  t1.ID, t1.Name, t1.Bit
from
  [Table 1] t1
  left join [Table 2] t2 on t2.ID = t1.ID
where
  t1.Bit = 1 or t2.Bit = 0

union all

select
  t2.ID, t2.Name, t2.Bit
from
  [Table 2] t2
  left join [Table 1] t1 on t1.ID = t2.ID
where
  t1.bit = 0 and t2.Bit = 1

(If there is a True in both tables or a False in both tables for an item, the record from Table 1 is used.)

Upvotes: 1

Related Questions