Selecting one row with multiple children
I would like to think that I have a good understanding of mysql and always try to ensure I do things in the correct and most optimized way which is why Im posting. I cant get my head around how to do this query or if its even possible in one query so if anyone could help that would be great.
I have two db tables for example "users" and "pets". Each row of the "pets" table has a foreign key referencing the id of a user. Each "user" may have multiple pets. Each "pet" only belongs to one "user".
I want to select all "users" and also all "pets" belonging to each "user".
I would usualy do one query that selects all "users" and then loop through the results and within each row/loop I would run another query and select all "pets" belonging to that "user" and start building up my multi dimensional array.
I dont believe running mysql queries inside a loop is a good idea let alone a loop that could potentialy be thousands of iterations.
Can anyone help me with the theory of how I should tackle this? Even if it means changing the structure of my tables.