SQL Two Tables with One Unique column

Question

I have two tables tbl_backupchecks and tbl_backupchecks_sqlbak.

Both tables have two column names, Company and ServerName.

I'd like to return a result set comprising of Company and Servername but in a merged list. Essentially if there is a servername and companyname in tbl_backupchecks_SQLBak that is not in tbl_backupchecks I want to report on it as well.

In basic terms I want a resultset of rows that is two column; company and servername. I tried writing a left join but it bought back two columns called ServerName. Can this be done?

If a row exists in Both tables, should it appear in your results once or twice? — MatBailie, Dec 01 '11 at 15:07
I think this page describes the problem and a solution well: http://www.simple-talk.com/sql/performance/the-except-and-intersect-operators-in-sql-server/ — Niklas, Dec 01 '11 at 15:13

score 5 · Accepted Answer · answered Dec 01 '11 at 15:07

5

Assuming that your columns Company and Servername are datatypes that can be DISTINCT, you could use a UNION query:

SELECT Company, Servername FROM tbl_backupchecks
UNION
SELECT Company, Servername FROM tbl_backupchecks_sqlbak

answered Dec 01 '11 at 15:07

Anthony Grist

37,856
8
63
74

score 0 · Answer 2 · answered Dec 01 '11 at 15:07

0

I think a union should do that for you

   SELECT Company, ServerName
   FROM tbl_backupchecks
   UNION
   SELECT Company, ServerName
   FROM tbl_backupchecks_sqlbak

answered Dec 01 '11 at 15:07

SGB

616
6
10

score 0 · Answer 3 · answered Dec 01 '11 at 15:07

0

Use UNION:

SELECT  Company, ServerName 
  FROM  tbl_backupchecks 
UNION
SELECT  Company, ServerName 
  FROM  tbl_backupchecks_sqlbak

answered Dec 01 '11 at 15:07

Klas Lindbäck

32,669
4
56
80

score 0 · Answer 4 · answered Dec 01 '11 at 15:07

I think you want a UNION:

For distinct rows between both tables use this:

SELECT Company,ServerName
FROM tbl_backupchecks
UNION
SELECT Company,ServerName
FROM tbl_backupchecks_sqlbak

For all rows from both tables (will not remove duplicate rows that exist in both tables) use this:

SELECT Company,ServerName
FROM tbl_backupchecks
UNION ALL
SELECT Company,ServerName
FROM tbl_backupchecks_sqlbak

score 0 · Answer 5 · answered Dec 01 '11 at 15:08

0

SELECT Company, ServerName FROM tbl_backupchecks

UNION

SELECT Company, ServerName FROM tbl_backupchecks_sqlbak

(Change UNION to UNION ALL if you want to keep the duplicates.)

answered Dec 01 '11 at 15:08

MatBailie

77,948
17
98
134

score 0 · Answer 6 · answered Dec 01 '11 at 15:08

0

If you only want unique records from both, use this:

SELECT DISTINCT Company, ServerName
FROM
(
SELECT Company, ServerName FROM tbl_backupchecks
UNION
SELECT Company, ServerName FROM tbl_backupchecks_sqlbak
) A

answered Dec 01 '11 at 15:08

flayto

955
2
9
19

score 0 · Answer 7 · edited Dec 01 '11 at 15:33

0

I think you want a union of the 2 tables. Something like:

SELECT Company, ServerName FROM tbl_backupchecks
UNION 
SELECT Company, ServerName FROM tbl_backupchecks_SQLBak

edited Dec 01 '11 at 15:33

Justin

82,493
48
216
356

answered Dec 01 '11 at 15:09

user1075709

1

score 0 · Answer 8 · answered Dec 01 '11 at 15:09

Here is a solution that also tell you where the record is

select Company, ServerName, sum(case when is_bak=0 then 0 else 1) as in_bak, sum(case when is_bak=0 then 1 else 0) as in_not_bak from (
select Company, ServerName,0 as is_bak from tbl_backupchecks union 
select  SELECT Company, ServerName, 1 as is_bak FROM tbl_backupchecks_SQLBak
) group by Company, ServerName

SQL Two Tables with One Unique column

8 Answers8