Heero Yuy Heero Yuy - 5 days ago 6
MySQL Question

How to query without doing N+1 query in MySQL

i have 2 tables

users_table
id | first_name
1 | first name1
2 | first name2

addresses_table
id | user_id | address
1 | 1 | first address of user 1
2 | 1 | second address of user 1
3 | 2 | first address of user 2
4 | 2 | second address of user 2


I want a table result like this, first column is the users_table first_name and the second column are the hobbies of each user separated by comma

<table>
<tr>
<td>first name1</td><td>first address of user 1, second address of user 1</td>
</tr>
<tr>
<td>first name2</td><td>first address of user 2, second address of user 2</td>
</tr>
</table>


if i have to query it will have to
select * from users_table
then get the addresses of each user like
select * from addresses_table where user_id = 1
which is not very efficient, this can be prevented with ORMs but i want to know we can prevent N+1 query using SQL

Answer

you can use the below query

select first_name , group_concat(address,',') as address from users_table usrTable join addresses_table addTable on usrTable.id = addTable.user_id group by first_name

Comments