CODEWITHSUNDEEP
sqlsqlitebetter-sqlite3
Yash
Yash

Reputation: 291

select query for one-to-many relations table?

I have 2 tables. These two tables have one-to-many relations.

TABLE - A

column1 column2
1       label1
2       label2

TABLE - B

Bcolumn1 Bcolumn2 Bcolumn3
1        value1   value4
1        value2   value5
2        value3   value6

RESULT TABLE


column1 column2 json
1       label1  [[value1,value4],[value2,value5]]
2       label2  [[value3,value6]]

I want to get RESULT TABLE1 using TABLE - A and TABLE - B.

how can I get this result?

Thank you.

Upvotes: 1

Views: 265

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

We could use GROUP_CONCAT here:

SELECT
    a.column1,
    a.column2,
    '[' || GROUP_CONCAT('[' || b.Bcolumn2 || ',' || b.Bcolumn3 || ']') || ']' AS json
FROM TableA a
LEFT JOIN TableB b
    ON b.Bcolumn1 = a.column1
GROUP BY
    a.column1,
    a.column2;

Demo

Upvotes: 2

Related Questions