Heero Yuy Heero Yuy - 1 year ago 65
MySQL Question

How to query without doing N+1 query in MySQL

i have 2 tables

id | first_name
1 | first name1
2 | first name2

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

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

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download