Often, i have to write SQL queries according to the following pattern:
SELECT A.*, GROUP_CONCAT(B.b_id SEPARATOR ';') b_list FROM A LEFT JOIN B ON B.a_id = A.a_id GROUP BY A.a_id
There exists a one-to-many relationship; one A can have multiple Bs. Now I want to list all the As and for each of them print the list of all Bs assigned to it at the same time.
My problem is that I want to display all the columns of B as well, and to get them I need to run an extra query fetching the data for all the b_ids which appear in one of the b_lists in the result set. To achieve this, I used to iterate over the result set two times: First, I collect all the b_ids needed, after that I run a second query
SELECT * FROM B WHERE b_id IN(...collected b_ids...) and save the result to an associative array with the b_id as the key. Next, I can easily iterate over the result set again and print the data by accessing my array containing the B columns.
Is there any better practice to achieve that? Of course I could join all the columns in the first query without the GROUP BY, but in general, my A and B tables are quite complex and I want to avoid redundancy that is caused by the A columns being "copied" for each B.