需要有关联接表的帮助 [英] Need help about joining tables

问题描述

我有一个MySQL数据库，我正在尝试创建一个Web界面来管理票证，现在我正试图像下面列出票证: [title] [创建票证的人的名称] [优先级] [创建日期] [负责此票证的人]

I have a MySQL database and I'm trying to create a web interface to manage tickets, right now I'm trying to list the tickets like so: [title][name of the person that created the ticket][priority][date created][peoples that are in charge of this ticket]

所以我有一个名为票据的表，其中包含标题，创建票据的人的ID，优先级和日期.

so I have a table named tickets with the title, the id of the person that created the ticket, the priority, the date.

我还有另一个名为users的表，您可以在其中找到名字和姓氏以及其ID的其他信息(您可以将两个表与该ID链接起来)

I have another table named users where you can find the first and last name and some other informations with their ID (you can link the two tables with that ID)

我还有另一个名为tickets_users的表，您可以在其中找到负责票证的人民的ID

I have another table named tickets_users where you can find the ID of the peoples that are in charge of the tickets

我的问题是我不知道如何将所有这些链接到一个请求中，这很简单，如果只有一个人负责机票，但可以有多个人，我尝试了一些查询，但是我总是得到当负责票务的人数超过一个时，票证标题等会翻倍.

My problem is I don't know how to link all of this in one request, it would be simple if only one people was in charge of a ticket but there can be multiple persons, I tried some queries but I always get tickets titles etc in double when there is more that one people in charge of a ticket.

预先感谢 编辑 表格示例:

Thanks in advance EDIT Example of the tables:

tickets:
   -id = 523 | title = help with internet explorer | priority = 3 | date = 2013-10-10 11:20:51
users: 
   -id = 25 | firstname = John | lastname = Light
   -id = 35 | firstname = Dwight | lastname = Night
   -id = 53 | firstname = Maria | lastname = Sun
tickets_users :
   -ticketid = 523 | userid = 25 | type = 1
   -ticketid = 523 | userid = 35 | type = 2
   -ticketid = 523 | userid = 53 | type = 2

我希望能够请求显示:

[help with internet explorer][John Light][3][2013-10-10 11:20:51][Maria Sun - Dwight Night]

在一行(每张票)中，对于我的数据库中的所有票

In one line (per ticket) and for all the tickets in my DB

推荐答案

您可以使用group_concat聚合函数将链接的人员的姓名分组到结果的单个字段中.由于我没有您确切的表结构，因此我组成了字段和表的名称.

You can use the group_concat aggregate function to group the names of the linked persons into a single field in the result. Since I don't have your exact table structure, I've made up the names of the fields and tables.

select
  t.title,
  group_concat(
    case when tu.type = 1 then 
      concat(u.firstname, ' ', u.lastname)
    end) as creator,
  t.priority,
  t.date,
  group_concat(
    case when tu.type = 2 then 
      concat(u.firstname, ' ', u.lastname)
    end SEPARATOR ' - ') as users
from
  tickets t
  inner join tickets_users tu on tu.ticketid = t.id
  inner join users u on u.id = tu.userid
group by
  t.id;

如果确实只有一个票证创建者(这很有意义)，那么我将给票证一个creatoruserid来引用John.在这种情况下，John不需要在联结表中，并且您实际上不再需要type列.

If there is indeed only one creator for a ticket (which makes sense), then I would give ticket a creatoruserid to refer to John. In that case, John doesn't need to be in the junction table, and you actually don't need the type column any more.

这篇关于需要有关联接表的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！