Get distinct individual column values (not distinct pairs) from two tables in single query

0
votes

I have two tables like the following. One is for sport talents of some people and second for arts talents. One may not have a sport talent to list and same applies for art talent.

CREATE TABLE SPORT_TALENT(name varchar(10), TALENT varchar(10));
CREATE TABLE ART_TALENT(name varchar(10), TALENT varchar(10));

INSERT INTO SPORT_TALENT(name, TALENT) VALUES
  ('Steve', 'Footbal')
 ,('Steve', 'Golf')
 ,('Bob'  , 'Golf')
 ,('Mary' , 'Tennnis');

INSERT INTO ART_TALENT(name, TALENT) VALUES
  ('Steve', 'Dancer')
, ('Steve', 'Singer')
, ('Bob'  , 'Dancer')
, ('Bob'  , 'Singer')
, ('John' , 'Dancer');

Now I want to list down sport talent and art talent of one person. I would like to avoid duplication. But I don't mind if there is a "null" in any output. I tried the following

select distinct sport_talent.talent as s_talent,art_talent.talent as a_talent
from sport_talent
JOIN art_talent on sport_talent.name=art_talent.name
where (sport_talent.name='Steve' or art_talent.name='Steve');

 s_talent | a_talent 
----------+----------
 Footbal  | Dancer
 Golf     | Singer
 Footbal  | Singer
 Golf     | Dancer

I would like to avoid redundancy and need something like the following (distinct values of sport talents + distinct values of art talents). 

 s_talent | a_talent 
----------+----------
 Footbal  | Dancer
 Golf     | Singer

As mentioned in subject, I am not looking for distinct combinations. But at the same time, it's OK if there are some records with "null" value in one column. I am relatively new to SQL.

3
sqlpostgresqlouter-joindistinct-values
So, in your result, there is no relationship between the s_talent values and the a_talent values. You simply have two independent lists shown side-by-side? – Turophile
@Vimt if you look data steve played football and golf, if you join this result with art talent for steve it has dancer and singer, therefore sing played football and golf and singer played golf and football. it's what explain the 4 rows résults – Eric Siodmak
Are you running the current version Postgres 9.4? (Always add your version!) – Erwin Brandstetter
@Turophile: Yes, two idependent lists shown side by side. No relation between s_talent and a_talent. Anyway I got the answer from Erwin and kordirko – Vimt

3 Answers

0
votes

Try:

SELECT s_talent, a_talent
FROM (
  SELECT distinct on (talent) talent as s_talent,
        dense_rank() over (order by  talent) as x
  FROM SPORT_TALENT
  WHERE name='Steve'
) x
FULL OUTER JOIN (
  SELECT distinct on (talent) talent as a_talent,
        dense_rank() over (order by  talent) as x
  FROM ART_TALENT
  WHERE name='Steve'
) y
ON x.x = y.x

Demo: http://sqlfiddle.com/#!15/66e04/3

0
votes

There are no duplicates in your query. Each of the four records in your query return is unique. This result may not be what you want, but seems like its problem is not the duplicate.

0
votes

Postgres 9.4

... introduces unnest() with multiple arguments. Does exactly what you want, and should be fast, too. Per documentation:

The special table function UNNEST may be called with any number of array parameters, and it returns a corresponding number of columns, as if UNNEST (Section 9.18) had been called on each parameter separately and combined using the ROWS FROM construct.

About ROWS FROM:

SELECT *
FROM   unnest(
         ARRAY(SELECT DISTINCT talent FROM sport_talent WHERE name = 'Steve')
       , ARRAY(SELECT DISTINCT talent FROM art_talent WHERE name = 'Steve')
       ) AS t(s_talent, a_talent);

Postgres 9.3 or older

SELECT s_talent, a_talent
FROM  (
   SELECT talent AS s_talent, row_number() OVER () AS rn
   FROM   sport_talent
   WHERE  name = 'Steve'
   GROUP  BY 1
   ) s
FULL JOIN (
   SELECT talent AS a_talent, row_number() OVER () AS rn
   FROM   art_talent
   WHERE  name = 'Steve'
   GROUP  BY 1
   ) a USING (rn);

Similar previous answers with more explanation:

This is similar to what @kordirko posted, but uses GROUP BY to get distinct talents, which is evaluated before window functions. So we only need a bare row_number() and not the more expensive dense_rank().

About the sequence of events in a SELECT query:

SQL Fiddle.