CODEWITHSUNDEEP
sql
Julio Venero
Julio Venero

Reputation: 3

Select columns from different tables without repeating rows

For this example tables:

Table A:

Col1   Col2
-----------
2015   A
2015   B
2015   C

Table B:

Col1    Col2
------------ 
2015    X
2015    Y
2015    Z

I want a query that returns:

Col1    Col2
------------
A       X
B       Y
C       Z

I have tried something like this:

SELECT TA.COL2, TB.COL2
FROM
  (SELECT * FROM TABLE_A WHERE COL1=2015) TA,
  (SELECT * FROM TABLE_B WHERE COL1=2015) TB,

But I'm getting duplicated results

Col1    Col2
-----------
A       X
A       Y
A       Z
B       X
B       Y
B       Z
C       X
C       Y
C       Z

Upvotes: 0

Views: 145

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

A union all/group by approach may be what you want:

SELECT MAX(COL2_a) as COL2_a, MAX(COL2_B) as COL2_B
FROM ((SELECT COL2 as COL2_A, NULL as COL2_B,
              ROW_NUMBER() OVER (ORDER BY COL2) as seqnum
       FROM TABLE_A
       WHERE COL1 = 2015
      ) UNION ALL
      (SELECT NULL, COL2, ROW_NUMBER() OVER (ORDER BY COL2) as seqnum
       FROM TABLE_B
       WHERE COL1 = 2015
      ) UNION ALL
     ) t
GROUP BY seqnum;

Alternatively, use a FULL JOIN:

SELECT a.COL2 as COL2_a, b.COL2 as COL2_B
FROM (SELECT a.*,
             ROW_NUMBER() OVER (ORDER BY COL2) as seqnum
      FROM TABLE_A a
      WHERE COL1 = 2015
     ) a FULL JOIN
     (SELECT b.*,
             ROW_NUMBER() OVER (ORDER BY COL2) as seqnum
      FROM TABLE_B b
      WHERE COL1 = 2015
     ) b
     ON a.seqnum = b.seqnum;

Both these methods returns all values, if one table has more values than the other.

Upvotes: 0

Kenney Hill
Kenney Hill

Reputation: 81

Not sure why you want this but here is my method for doing it.

SELECT
    TA.COL2 AS Col1,
    TB.COL2 AS Col2
FROM
    (SELECT COL1, COL2, ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) AS Seq FROM TABLE_A) TA
JOIN
    (SELECT COL1, COL2, ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) AS Seq FROM TABLE_B) TB
    ON TA.COL1 = TB.COL1
        AND TA.Seq = TB.Seq

Upvotes: 0

Anton Ragot
Anton Ragot

Reputation: 181

A way to do it is to use the row number:

SELECT TA.COL2, TB.COL2
FROM
  (SELECT TABLE_A.COL2, ROWNUM AS R1 FROM TABLE_A WHERE COL1=2015) TA,
  (SELECT TABLE_B.COL2, ROWNUM AS R2 FROM TABLE_B WHERE COL1=2015) TB,
WHERE T1.R1 = T2.R2

Upvotes: 2

DRV
DRV

Reputation: 756

In a SELECT statement, include DISTINCT or DISTINCTROW keyword after the SELECT clause. For More Details: http://www.geeksengine.com/database/basic-select/eliminate-duplicate-rows.php

Upvotes: 0

Related Questions