MySQL select multiple tables
I want to select multiple rows from sub tables but keep everything in one row.
The main table will only ever have one row, all the other tables may contain more than one row. So I want to try and concat the values into a single field so the recordset only contains 1 row.
Most of the tables are directly linked by id to the main table but some tables are pulling data from other tables linked by a separate id
SELECT * FROM table_1
JOIN table_2 on table_1.id=table_2.id
JOIN table_3 on table_1.id=table_3.id
JOIN table_4 on table_3.sub_id=table_4.sub
I was trying group_concat but that still replicated the rows to the number of results from the other tables.
The main table will only ever have one row, all the other tables may contain more than one row. So I want to try and concat the values into a single field so the recordset only contains 1 row.
Most of the tables are directly linked by id to the main table but some tables are pulling data from other tables linked by a separate id
SELECT * FROM table_1
JOIN table_2 on table_1.id=table_2.id
JOIN table_3 on table_1.id=table_3.id
JOIN table_4 on table_3.sub_id=table_4.sub
I was trying group_concat but that still replicated the rows to the number of results from the other tables.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Not to my knowledge. I don't always keep up on SQL, so there might be a way that I don't know about, but I've always treated each subquery as returning a single value (or a list of values when you're using the IN operator).
Just given the operation of grouping, it seems like you wouldn't be able to get multiple, disparate, grouped columns in a single subquery.
That said, you could always do it in a stored procedure that selects all the columns from each table based on the ID key, then loops through the result set with a cursor and build out variables containing the comma-separated list of data for each column. Once all the variables are built out, return a single row with all of them.
Just given the operation of grouping, it seems like you wouldn't be able to get multiple, disparate, grouped columns in a single subquery.
That said, you could always do it in a stored procedure that selects all the columns from each table based on the ID key, then loops through the result set with a cursor and build out variables containing the comma-separated list of data for each column. Once all the variables are built out, return a single row with all of them.
ASKER
Does the job and it's fast enough
ASKER
Anyway around this?