union and join on the same table-CodePudding

I have a table that looks like this: TableA

 id| origin1 | origin2 | col4 ....
 1 | a       | a       | 434
 2 | c       | b       | 439
 3 | d       | d       | 8734

I want to get all the unique values between col b and col c, I used union for this:

SELECT *
FROM (
  SELECT origin1 as url, 'home' as source
  FROM TableA 
  UNION 
  SELECT origin2 as url, 'home2'
  FROM TableA
) AS data

I need to get the id per each row, in order to join the results to several tables including table a -

select id_1 as id, url, col 4,....
FROM (
  SELECT origin1 as url, 'home' as source, id as id_1
  FROM TableA
  UNION
  SELECT origin2 as url, 'home2', id
  FROM TableA 
) AS data
left join TableA on id_1 = TableA.id

expected results:

 id| url | col4 ....
 1 | a   | 434
 2 | c   | 439
 2 | b   | 439
 3 | d   | 8734

Actual results - too many rows are added by the id.

is it possible to add the id for each row without duplication?

CodePudding user response：

The problem is 'home' as source and 'home2' in your UNION, you have different source for each column, thus, you will never get an 'unique' result.
change it to,

select id_1 as id, url, col 4,....
FROM (
  SELECT origin1 as url, 'home' as source, id as id_1
  FROM TableA
  UNION
  SELECT origin2 as url, 'home', id
  FROM TableA 
) AS data
left join TableA on id_1 = TableA.id

CodePudding user response：

You may use NOT EXISTS with your union query as the following:

SELECT id, url, source, col4
FROM
(
  SELECT id, origin1 AS url,'home' as source, col4 
  FROM TableA
  UNION ALL
  SELECT id, origin2,'home2', col4 
  FROM TableA T
  WHERE NOT EXISTS(SELECT 1 FROM TableA D WHERE D.id=T.id AND D.origin2=T.origin1)
) T
ORDER BY id, url