Question 20740 in Mysql Question posted in
The official MySQL documentation can be found here.

Many-to-Many MySQL Relationship

Good Morning,

I’m after some help please 🙂
I have a MySQL database with the following tables: incidents, users and incident_users.

My incidents and users table are defined through a many-to-many relationship using the incident_users table.

So, when a user adds an incident, the incident_id and user_id are captured. It is possible for an incident_id to be associated with multiple user_id.

I’m looking to return a result like so:

incident_id user_fullname
1 John Smith
2 Jane Doe & John Smith
3 Robert Doe

Where user_fullname is a column from the users table and the grouping by incident_id

Tags:

2

Answers


  1. 0

    This will be slightly vague as you have not shared the full details of your tables. But the basic idea should be along the lines of :

    select incident_id, group_concat(user_name separator ' & ') as user_fullname ... group by incident_id

    See also: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

    Login or Signup to reply.
  2. 0

    Assuming your incident_users table is made up of a incident_id and a user_id

    then its a simple case of using the incident_id from this table and JOINing it to the User table

    SELECT i.incident_id, u.user_fullname
FROM incident i
    LEFT JOIN user u ON u.id = i.user_id

    If you have a firstname and lastname column but no fullname column, you can build a concatenation of the 2 you have as part of the query like this

    SELECT i.incident_id, CONCAT(u.firstname, ' ', u.lastname) as user_fullname
FROM incident i
    LEFT JOIN user u ON u.id = i.user_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search