CODEWITHSUNDEEP
postgresql
HSchmale
HSchmale

Reputation: 1929

SQL: Adding Aliases to a column value

I have 2 tables, one is a set of real names, the other has a collection of aliases for those people. I want to select from the real name table, but include all of the values in the alias table for each value in the realname. How would I go about this?

CREATE TABLE PEOPLE(
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR(64)
);
INSERT INTO PEOPLE(NAME)VALUES('BRUCE'),('CLARK'),('STEVE');

CREATE TABLE ALIASES(
  ID SERIAL PRIMARY KEY,
  PID INTEGER REFERENCES PEOPLE(ID),
  NAME VARCHAR(64)
);
INSERT INTO ALIASES(PID,NAME)VALUES
(1,'BATMAN'),(2,'SUPERMAN'),(3,'CAPTAIN AMERICA'),(1,'THE DARK KNIGHT');

I would like the result to look the below, so that I can do a full text search on that column value.

> SELECT NAME FROM PEOPLEALIASED;
NAME
-----
BRUCE BATMAN THE DARK KNIGHT
CLARK SUPERMAN
STEVE CAPTAIN AMERICA

Upvotes: 1

Views: 810

Answers (1)

Andomar
Andomar

Reputation: 238176

You can do that with string_agg():

select  p.name || string_agg(a.name, ' ')
from    people p
join    aliases a
on      p.id = a.pid
group by
        p.name

Example at SQL Fiddle.

Upvotes: 1

Related Questions