1

It has been a while since I've taken a RDB class and I have forgotten most sql that I have learned. I have a many to many relationship and I've made tables that look like this:

events:

event_id |     date     | event_name
    1    | "2015-01-01" | "meeting"
    2    | "2015-01-02" | "concert"
ect...

people:

people_id | name
     1    | "Matt Watson"
     2    | "Ray Man"
     3    | "Foo Boy"
etc...

participants:

part_id | event_id | people_id
    1   |     1    |     1
    2   |     1    |     2
    3   |     1    |     3

I don't really care about the names at the moment but how do I get out put looking like this:

event_id |     date     | event_name | people_id1 | people_id2 | ... | people_idn
    1    | "2015-01-01" |  "meeting" |      1     |      3     | ... |    102

I've figured out how to get just one person's id connected to the event by using a left join on event_id like the below code:

select * from events e left join participants p on e.event_id = p.event_id;

but this leaves me with many rows with the same event id and multiple people ids:

event_id |     date     | event_name | part_id | event_id | people_id 
    1    | "2015-01-01" |  "meeting" |    1    |     1    |     1
    1    | "2015-01-01" |  "meeting" |    2    |     1    |     2   
    1    | "2015-01-01" |  "meeting" |    3    |     1    |     3

Now if I join again I get double the rows but the columns start to add up in a way that I want. I feel like this is the wrong way of going about it. Is the only way to get the result I want is to join a bunch of times and figure out some way of getting rid of duplicate rows?

Panda Ninja
  • 65
  • 6
  • 2
    what you want is achieved by creating a PIVOT. unfortunately, mySQL does not have a PIVOT operator like other engines. the alternative is to do it by hand which will make it static in terms of the number of people_idX. – PandaCheLion Mar 22 '22 at 17:45
  • 1
    https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – xQbert Mar 22 '22 at 17:56

0 Answers0