Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Select multiple rows into one row from 2 tables

I have 2 tables:

–BEAT TABLE–

CREATE TABLE beat (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  artist_name TEXT NOT NULL,
  genre TEXT
);

— TAG TABLE —

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

CREATE TABLE tag (
  id BIGSERIAL PRIMARY KEY,
  beat_id BIGINT REFERENCES beat (id) NOT NULL,
  tag_name TEXT NOT NULL
);

One beat can have 2 tags for example. So, when I select beat I use next query:

SELECT
  *
FROM
  beat
  LEFT JOIN tag ON tag.beat_id = beat.id
WHERE beat.id = 1;

But this query returns me 2 rows: beat and tag with id 1 and second row beat + tag with id 2.

But I want to get it as single row: beat + tag with id 1 + tag with id 2.

UPD:
— Example data —

INSERT INTO beat (title, artist_name, genre) VALUES ('Beat-1', 'Artist-1', 'electro;

INSERT INTO tag (beat_id, tag_name) VALUES (1, 'Tag-1');

INSERT INTO tag (beat_id, tag_name) VALUES (1, 'Tag-2');

— Expected Output —

id 1
title Beat-1
artist_name Artist-1
genre electro
id 1
beat_id 1
tag_name Tag-1
id 2
beat_id 1
tag_name Tag-2

>Solution :

SELECT
  *  
FROM
  beat b
left join lateral (
    select json_agg(json_build_object('id',t.id, 'tag',t.tag_name)) as tags
    from tag t
    where t.beat_id = b.id
    ) c on true
WHERE b.id = 1
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading