'Select all related records when using group by in mySQL?
So I have table A and B
Obejcts
- id
- someId
...
TableB
- table_A_Id
So A has one to many relationship with B.
I wanna select from Table A grouped by someId left joining Table B. However if I do that, only one row is returned for each item selected from Table A. Can I get all records from table B for each record of A or does this require two queries?
So I want something like this:
- TableARecord1
RelatedTableBrecord1
RelatedTableBrecord2
But because of the grouping, this is what I get:
- TableARecord1
RelatedTableBrecord1
Can I do this with MySQL 5.x?
EDIT:
The tables are huge and the query is generated by an ORM so it's also huge, but basically I'm doing this:
SELECT table1.id, COUNT(DISTINCT(table1.someId)) as counter, table2.instanceId FROM table1 LEFT JOIN table2 ON table1.id=table2.table1Id GROUP BY table1.someId
This returns records like this:
{
id: 1,
counter: 10,
relatedItemsFromTable2: [
instanceId
]
}
The array always has 1 item. But there are more, should be:
{
id: 1,
counter: 10,
relatedItemsFromTable2: [
instanceId,
instanceId2,
instanceId3
]
}
I'm not sure if this is enough info. If not, please let me know and I'll post some simplified example tables and query.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|