如何根据联接数组的值为INNER JOIN表选择列 [英] How to select the columns for INNER JOIN table depending on values of the joined arrays

问题描述

我在本地服务器上使用phpMyAdmin创建了一个mySQL数据库.在这个数据库中，我存储了我的朋友的名字和最喜欢的NBA球队，这显然是多对多的关系.因此，我在MySQL中运行以下脚本来为此数据库创建适当的表:

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');

运行一个从数据库中获取数据并打印它们的PHP脚本之后，我想为我的每个朋友提供以下有效的json输出:

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"] 
}

我怎样才能使每个使用MySQL的人都喜欢的团队呢?

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

P.S.我当前的问题是受以下已回答问题的启发:

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

推荐答案

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

这篇关于如何根据联接数组的值为INNER JOIN表选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！