I have this query:
SELECT
GROUP_CONCAT(DISTINCT
`persons`.`name`,
'[START_Name_END]',
`persons`.`id`,
'[START_Id_END]',
`persons`.`isteacher`,
'[START_IsTeacher_END]'
SEPARATOR '[START_ROWSEPERATOR_END]') AS persons_values,
`locations`.`name` AS locations_name,
`locations`.`id` AS locations_id
FROM `locations`
INNER JOIN `locations_persons` ON `locations_persons`.`location_id` = `locations`.`id`
INNER JOIN `persons` ON `persons`.`id` = `locations_persons`.`person_id`
GROUP BY `locations`.`id`
ORDER BY `locations`.`id`
The reason why I use GROUP_CONCAT is to get all rows from persons table, that have reference in the association table, in the same column
I use [START_Name_END], [START_Id_END] to split the result later in my code and [START_ROWSEPERATOR_END] to determine if it's same row or a new one.
Everything works just fine so far.
The thing is if any row in locations table has no reference to any row in persons table, I'm NOT getting that row in the results!
I beleive that the problem is in JOIN, what should I use to fetch all rows regardless if that row has reference in the association table or not?
Any help would be greatly appreciated!
Try using:
LEFT OUTER JOIN `locations_persons` ...
-- Edit
Sorry, I'm new at answering these StackOverflow things and didn't give this the attention that it deserves before responding.
Your table structure is a classic many-to-many join, so if there is no person for a given location, then there would only be location data in the locations table. As such, you would need to have a LEFT OUTER JOIN for both the join on locations_persons AND the join from locations_persons to persons.
If you're still having troubles I'll create a quick MySQL database and test things out.. I'll do that from now on, but thought I should reply more fully now because of my bad partial answer earlier.
--Edit2
Regarding expecting issues: When you left outer join, any join that fails (i.e. there's rows on the left side, but no match on the right side) will have the tables from the right returning all null values. In this case, you would want your query to properly handle all fields in locations_persons and persons returning null values.
If GROUP_CONCAT doesn't deal with this well (I'm not familiar with it), you might need to wrap these persons.* fields in an IFNULL, if you're having problems:
SELECT
GROUP_CONCAT(
DISTINCT `persons`.`name`,
'[START_Name_END]',
IFNULL(`persons`.`id`, ''),
'[START_Id_END]',
IFNULL(`persons`.`isteacher`, ''),
'[START_IsTeacher_END]' SEPARATOR '[START_ROWSEPERATOR_END]'
) AS persons_values,
`locations`.`name` AS locations_name,
`locations`.`id` AS locations_id
FROM
`locations`
LEFT OUTER JOIN `locations_persons` ON
`locations_persons`.`location_id` = `locations`.`id`
LEFT OUTER JOIN `persons` ON
`persons`.`id` = `locations_persons`.`person_id`
GROUP BY
`locations`.`id`
ORDER BY
`locations`.`id`
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments