CODEWITHSUNDEEP
sqldistinct
Bob Fishel
Bob Fishel

Reputation: 123

SQL Select distinct values from multiple tables

So here's my setup I have 2 tables Old and New with the following (simplified) schemas

Old
[ManName]
[ManNumber]

New
[Manager_Name]
[Manager_Number]

I'm looking to craft a SQL query that returns the following but in 4 columns in 1 query as opposed to 2 queries with 2 columns each 

select distinct manname, mannumber from OLD 
select distinct Manager_Name, Manager_Number from NEW

So my ideal Result Set would have 4 columns: 

ManName ManNumber Manager_Name Manager Number

Thanks!

Upvotes: 2

Views: 35508

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

Using pretty much any RDBMS except MySQL, you can do it with CTEs and ROW_NUMBER();

WITH cteOld AS (
  SELECT DISTINCT ManName, ManNumber, 
         ROW_NUMBER() OVER (ORDER BY ManNumber) rn FROM Old
), cteNew AS (
  SELECT DISTINCT Manager_Name, Manager_Number,
         ROW_NUMBER() OVER (ORDER BY Manager_Number) rn FROM New
)
SELECT ManName, ManNumber, Manager_Name, Manager_Number 
FROM cteOld
FULL OUTER JOIN cteNew
  ON cteOld.rn=cteNew.rn

An SQLfiddle to test with.

Another, easier, way to just get all of them is to simply use a UNION;

SELECT ManName, ManNumber, 0 isNew FROM Old
UNION 
SELECT Manager_Name, Manager_Number, 1 FROM New

Another SQLfiddle.

Upvotes: 2

aviad m
aviad m

Reputation: 186

have you tried a JOIN statement:

select distinct * 
from old a join new b on a.ManName = b. Manager_Name and a. ManNumber = b. Manager_Number

Upvotes: 2

Related Questions