How to, select a list of ids from another table and have it as a column in select in SQL

Question

Okay so I have two tables, with many columns, but for illustration purposes I have a and b:

a:           b:   
+---+-----+  +---+------+-----+
|id |name |  |id | a_id |size |
+---+-----+  +---+------+-----+

What I want is to SELECT * FROM a and then for each a' in a I want to SELECT id FROM b WHERE a_id = a'.id. But can I create a single curery such that the results becomes:

ab:
+---+-----+--------------------------------+ 
|id |name | b_idx_1, b_idx_2, ... b_idx_n  |
+---+-----+--------------------------------+

I tried: SELECT id, name, (SELECT id FROM b WHERE b.a_id = a.id) FROM a WHERE a.name LIKE 'Random Name' But then I get an error, with the sub-query returning more than one row.

Use `JOIN` then `GROUP_CONCAT` to concatenate `b_id` values from multiple rows into one. — Giorgos Betsos, Apr 05 '18 at 07:28

Jay Shankar Gupta · Answer 1 · 2018-04-06T06:36:09.190

1

SELECT a.id, a.name, GROUP_CONCAT(b.id) 
FROM a
INNER JOIN b
ON b.a_id = a.id
WHERE a.name LIKE 'Random Name'

edited Apr 06 '18 at 06:36

answered Apr 05 '18 at 07:28

Jay Shankar Gupta

5,777
1
8
26

There is missing an `ON` in the query and could you give an explanation of how it works? Then I will accept this answer :) – Lars Nielsen Apr 06 '18 at 06:27

score 0 · Answer 2 · answered Apr 05 '18 at 07:27

0

try this join query

select a.id,a.name,b.* from a join b on a.id=b.a_id

answered Apr 05 '18 at 07:27

Bhargav Chudasama

6,348
3
19
37

How to, select a list of ids from another table and have it as a column in select in SQL

2 Answers2