How to select the columns for INNER JOIN table depending on values of the joined arrays

Question

I created a mySQL database with phpMyAdmin in my local server. In this database I store the names and the favourite NBA teams of my friends.This is obviously a many-to-many relationship. For this reason, I run the followed script in MySQL to create the appropriate tables for this database:

CREATE TABLE `friends` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `teams` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `relations` (
  `friends_id` int(4) NOT NULL,
  `teams_id` int(4) NOT NULL,
  `status` varchar(30) NOT NULL
);

Obviously, I inserted some values to these tables but I do not provide extensively the source code here so as to save some space. An small piece of it is the following:

INSERT INTO `friends` (`id`, `name`)
VALUES
    (1,'David Belton'),
    (2,'Alex James');

INSERT INTO `teams` (`id`, `name`)
VALUES
    (1,'Cleveland Cavaliers'),
    (2,'Boston Celtics'),
    (3,'Houston Rockets');

INSERT INTO `relations` (`friends_id`, `teams_id`, `status`)
VALUES
    (1,1, 'Current'),
    (2,1, 'Current'),
    (2,2, 'Past'),
    (2,3, 'Past');

After running a PHP script that fetches the data from the database and print them, I want to have the following kind of valid json output for each of my friends:

{
    "id": "1",
    "name": "Alex James",
    "Current team": ["Boston Celtics"]
    "Past team": [ "Cleveland Cavaliers", "Houston Rockets"] 
}

How can I make this array of favourite teams for each person with MySQL?

P.S. My current question is inspired by the following answered question: How to join arrays with MySQL from 3 tables of many-to-many relationship

ydlgr · Accepted Answer · 2018-03-16T06:20:21.590

1

SELECT 
   CONCAT( 
    "{"
   ,     '"id"' , ":" , '"' , friends.id , '"' , ","
   ,     '"name"' , ":" , '"' , friends.name , '"' , ","
    , 
   CASE 
   WHEN relations.status = 'Current' 
     THEN CONCAT('"CurrentTeam":["',    teams.name ,'"]')
   ELSE CONCAT('"pastTeam": '   ,   '[' ,   GROUP_CONCAT( '"',teams.name, '"'),']'  )
     END   
   , "}"
   )
  AS json
FROM 
 friends 
INNER JOIN 
 relations 
ON 
 friends.id = relations.friends_id
INNER JOIN
 teams 
ON
relations.teams_id = teams.id
 group by friends.id,relations.status

http://sqlfiddle.com/#!9/694bc69/23

edited Mar 16 '18 at 06:20

answered Mar 15 '18 at 08:55

ydlgr

57
1
6

1

Thanks for the response. However it would be good to write it more analytically and integrate it to the answer of the linked question because it does not give the output that I want to. – Outcast Mar 15 '18 at 08:57
Hi again, I edited my answer and shared second sqlfiddle link . – ydlgr Mar 15 '18 at 09:07
I checked your post and edited my answer again. I think this is you wanted, right ? – ydlgr Mar 15 '18 at 12:41
Your answer is very good but it is still missing something principal in my question. Read again my question: I want `{"id":"2","name":"Alex James","pastTeam":[ "Cleveland Cavaliers", "Houston Rockets"]}` not seperately `{"id":"2","name":"Alex James","pastTeam":[""Boston Celtics"]}` and `{"id":"2","name":"Alex James","pastTeam":[""Houston Rockets"]}`. Can you do this? – Outcast Mar 15 '18 at 21:34
Hi Poete, I misunderstood your question :) Now, its working very well. http://sqlfiddle.com/#!9/694bc69/23 – ydlgr Mar 16 '18 at 06:20
Yes, that's it. Just note that because someone may have multiple current teams (it may happen!!), I would suggest you to do GROUP_CONCAT also for `WHEN relations.status = 'Current'` so that your answer is absolutely complete. – Outcast Mar 16 '18 at 09:06

score 0 · Answer 2 · answered Mar 15 '18 at 09:12

For these type of query you have to use the mysql version 5.7 using these you are able to use the json type & function in query..

Answer :

SELECT GROUP_CONCAT( JSON_OBJECT(
'id', f.id,  'fname', f.name,  'tname', t.name,  'current_status', r.status), (SELECT JSON_OBJECT('name', tm.name)
FROM teams tm, relations re
WHERE tm.id = re.teams_id
AND f.id = re.friends_id
AND re.status =  "Past"))
FROM teams t, relations r, friends f
WHERE t.id = r.teams_id
AND f.id = r.friends_id
AND r.status =  "Current"

How to select the columns for INNER JOIN table depending on values of the joined arrays

2 Answers2

Linked