AlexGH AlexGH - 5 months ago 10
SQL Question

Get original data from different tables

I have a table Customers, and a table Salesman, inside both a name column:

Table Customers Salesman Table
--name-- --name--
Peter Charlie
Vlado Clint
Landon Tim
Armand


if I use this query:

select a.name as CustomerName, b.name as SalesmanName from Customers a, Salesman b


then I get this:

CustomerName SalesmanName
Peter Charlie
Vlado Charlie
Landon Charlie
Peter Clint
Vlado Clint
Landon Clint
Peter Tim
Vlado Tim
Landon Tim
Peter Armand
Vlado Armand
Landon Armand


But want to get is this:

CustomerName SalesmanName
Peter Charlie
Vlado Clint
Landon Tim
Armand


What should I do? Is possible to get those columns exactly as they are in their tables using a query?

Answer

You can use the ROW_NUMBER() function to generate an arbitrary row number useful for joining the two:

;with cust AS (select name,ROW_NUMBER() OVER(ORDER BY name) AS RN from Customers)
     ,sales AS (select name,ROW_NUMBER() OVER(ORDER BY name) AS RN from Salesman)
select a.name as CustomerName
     , b.name as SalesmanName 
from cust a
FULL JOIN sales b
  ON a.RN = b.RN
ORDER BY COALESCE(a.RN,b.RN)