0

I have a table "Cars" and a table "Person". A Person drives many Cars and a Car can be driven by many People so I have another table "Person_Car" which has both id's per row.

Car(id, name)
Person(id, name)
Person_Car(car_id, person_id)

How can I get a list of all people with the cars it drives (car names concatenated), something like this:

("John", "Car 1, Car 2, Car 3")
("Kate", "Car 2, Car 4, Car 5")
alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
Alan
  • 2,559
  • 4
  • 32
  • 53

2 Answers2

0

Example is here: http://sqlfiddle.com/#!15/ba949/1

Test data:

Create table Car(id int, name text);
Create table Person(id int, name text);
Create table Person_Car(car_id int, person_id int);

INSERT INTO Car VALUES (1, 'Car 1'),
  (2, 'Car 2'),
  (3, 'Car 3'),
  (4, 'Car 4'),
  (5, 'Car 5');

INSERT INTO Person VALUES(1, 'John'), (2, 'Kate');

INSERT INTO Person_Car VALUES (1,1), (2,1), (3,1), (2,2), (4,2), (5,2);

Your desired code:

SELECT p.name, array_to_string(array_agg(c.name), ',') FROM Person p 
    INNER JOIN Person_Car pc ON p.id=pc.person_id 
    INNER JOIN Car c ON c.id=pc.car_id 
    GROUP by p.name

Output:

John    Car 1,Car 2,Car 3
Kate    Car 2,Car 4,Car 5
alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
0

Just in case you want to avoid the GROUP BY

Option 1

WITH forienKeyTable AS
(
  SELECT pc.person_id, c.name 
  FROM Car c 
  JOIN Person_Car pc ON pc.car_id = c.id
)
SELECT p.name
    , array_to_string
        (ARRAY(
            SELECT fkt.name 
            FROM forienKeyTable fkt
            WHERE fkt.person_id = p.id
        )::text[], ','::text, 'empty'::text)
FROM Person p;

Option 2

SELECT p.name
    , array_to_string
        (ARRAY(
            SELECT c.name 
            FROM Car c 
            JOIN Person_Car pc ON pc.car_id = c.id
            WHERE pc.person_id = p.id
        )::text[], ','::text, 'empty'::text)
FROM Person p;
brightmatter
  • 162
  • 1
  • 10