Edit Question - KnowledgeHub

Title Body

I've been thinking about this for a while and its got to a point where I think its better to ask around and listen what other people think. Im bulding a system that stores locations on Mysql. Every location has a type and some locations have multiple addresses. The tables look something like this location - location_id (autoincrement) - location_name - location_type_id location_types - type_id - type_name (For example "Laundry") location_information - location_id (Reference to the location table) - location_address - location_phone So if i wanted to query the database for the 10 most recently added I would go with something like this: SELECT l.location_id, l.location_name, t.type_id, t.type_name, i.location_address, i.location_phone FROM location AS l LEFT JOIN location_information AS i ON (l.location_id = i.location_id) LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ORDER BY l.location_id DESC LIMIT 10 Right? But the problem is that if a location has more than 1 address the limit/pagination is not going to be accurrate, unless I "GROUP BY l.location_id", but that is going to show only one address for each place.. what happens with the places that have multiple addresses? So I thought the only way to solve this is by doing a query inside a loop.. Something like this (pseudocode): $db->query('SELECT l.location_id, l.location_name, t.type_id, t.type_name FROM location AS l LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ORDER BY l.location_id DESC LIMIT 10'); $locations = array(); while ($row = $db->fetchRow()) { $db->query('SELECT i.location_address, i.location_phone FROM location_information AS i WHERE i.location_id = ?', $row['location_id']); $locationInfo = $db->fetch

Tags (comma-separated) Cancel