Niroda Niroda - 1 year ago 67
MySQL Question

MySQL, join many to many relationship query

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!

Answer Source

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`
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download