I have two tables as follows
user table
| user_id | name |
|---|---|
| 1 | zia |
| 2 | john |
| 3 | raza |
subject table
| data_id | user_id | subject |
|---|---|---|
| 1 | 1 | Math |
| 2 | 1 | Chem |
| 3 | 1 | Bio |
| 4 | 2 | Math |
| 5 | 2 | Phy |
when I am querying data i am getting results like this:
[
{
"user_id": "1",
"name": "zia",
"subject": [
"Math",
"Chem",
"Bio"
]
},
{
"user_id": "2",
"name": "john",
"subject": [
"Math",
"Phy"
]
},
]
My query is as follows
SELECT
users.user_id,
users.name,
GROUP_CONCAT(subjects.subject) sub
FROM
`users`
INNER JOIN subjects ON users.user_id = subjects.user_id
GROUP BY
subjects.user_id;
but actually I want to get data in following way:
the resuluts shown above are in such a way that if an entry from user table does not have coresponding enteries in subject table even then we must have user name and user id in our rsults as follows
[
{
"user_id": "1",
"name": "zia",
"subject": [
"Math",
"Chem",
"Bio"
]
},
{
"user_id": "2",
"name": "john",
"subject": [
"Math",
"Phy"
]
},
{
"user_id": "3",
"name": "Raza",
}
]
Here as you see that we have data in such a way that all the enteries from user table are shown alog with subject enteries from subject table if they match otherwise every user table entery is showing up with no affect.
*PLease help me in solving this issue **
