0

I wan to find the data in single mysql query for following sceneraio

Suppose I have a users table:

id | name
 1 |   A
 2 |   B

events table:

id | event_name  
 1 |     E1
 2 |     E2

event_request_sent table:

id | event_id | user_id
 1 |     1    |    1
 2 |     1    |    2
 3 |     2    |    1

I need the output something like that for json output

[{
    "event_name": "E1",
    "user_Details": [{
        "name": "A",
        "id": "1"
    }, {
        "name": "B",
        "id": 2
    }]
}, {
    "event_name": "E2",
    "user_Details": [{
        "name": "A",
        "id": "1"
    }]
}]

Please help me, I tried using joins but getting 2 entries for event E1 and one for E2 as based in no of users in event_request_sent table. I am not getting the output like this.

lolbas
  • 794
  • 1
  • 9
  • 34
Anil
  • 101
  • 2
  • 15

1 Answers1

0
Select event.events_name, GROUP_CONCAT(user.name) AS name, GROUP_CONCAT(user.id) AS id
from user user_table,
event events_table,
eventreq events_request_table
where
event.id = eventreq.event_id
and user.id = eventreq.user_id

I assume this select query would run perfectly.

Farhan Qasim
  • 990
  • 5
  • 18
  • Specifying multiple tables in `FROM` clause is an [old-style JOINing](https://stackoverflow.com/a/13476050/3745677). – lolbas Jan 29 '18 at 11:29
  • @lolbas i'm a fresher in programming so i am still learning, but i guess this would work for this question, wouldn't it? I am sorry for my query as it might be unreadable by some experienced people. – Farhan Qasim Jan 29 '18 at 11:30
  • Farhan , Thanks for the answer, but I think you didn't get what I ask for. This is not what I need – Anil Jan 29 '18 at 11:33
  • @ak001 I've now used group concat, what it should to is that print the id and name in the same row against the event name, can you check it now if it this is what you want? – Farhan Qasim Jan 29 '18 at 11:57
  • This is not the solution , for more understanding you may refer my question again. & Thanks again for your efforts – Anil Jan 29 '18 at 12:54