关于连接表再次需要帮助 [英] Need help again about joining tables
问题描述
我问了一个有关MySQL请求的问题，可以在这里找到我的帖子:需要有关联接表的帮助
some context I asked a question about a MySQL request, my post can be found there: Need help about joining tables
现在我有另一个与此相关的问题，所以在我的页面上，我从数据库中列出了我的请求的票据:
Now I have another problem related to that, so on my page I'm listing tickets from a database my request is:
"SELECT
glpi_tickets.id,
glpi_tickets.name,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 1 THEN
CONCAT(glpi_users.firstname, ' ', glpi_users.realname)
END) AS creator,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 1 THEN
CONCAT(glpi_tickets_users.users_id)
END) AS creator_id,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 2 THEN
CONCAT(glpi_users.firstname, ' ', glpi_users.realname)
END) AS users,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 2 THEN
CONCAT(glpi_tickets_users.users_id)
END) AS users_id,
glpi_tickets.date,
glpi_tickets.priority,
glpi_tickets.date_mod,
glpi_itilcategories.completename,
glpi_tickets.status,
glpi_tickets.users_id_lastupdater,
GROUP_CONCAT(
CASE WHEN glpi_tickets.users_id_lastupdater = glpi_users.id THEN
CONCAT(glpi_users.firstname, ' ', glpi_users.realname)
END SEPARATOR '<br>') AS last_updater,
glpi_tickets.content
FROM
glpi_tickets
JOIN glpi_tickets_users ON glpi_tickets_users.tickets_id = glpi_tickets.id
JOIN glpi_users ON glpi_users.id = glpi_tickets_users.users_id
JOIN glpi_itilcategories ON glpi_itilcategories.id = glpi_tickets.itilcategories_id
GROUP BY
glpi_tickets.id"
结果如下:
[ID][Title][creator][date created][priority][category][status][date modified][assigned to][last update by]
[125][helpdesk test][admin][29-01-2013 21:09][low][messaging][new][30-01-2013 17:52][Tony][admin]
对于每张票证，创建者-creator_id和用户-users_id可以包含多个ID和名称.
For each ticket, creator - creator_id and users - users_id can contain multiple ID and name.
现在我想在另一页上显示用户信息，我使用了相同的请求，但是WHERE glpi_users.id = ?从users.php?id=x
Now I want to display user information in another page, I used the same request but with WHERE glpi_users.id = ? getting the id from users.php?id=x
除了创建者和用户之外，其他所有东西都可以正常工作，因为WHERE glpi_users.id = x仅选择一个人.
Everything works fine except the the creator and users, since the WHERE glpi_users.id = x only select one person.
我想要一种显示方式，就像在上一个请求中一样，在此先感谢
I'd like a way to display then like in the previous request, thanks in advance
尝试了GolezTrols解决方案，但是它不起作用，这是我的查询:
Tried the GolezTrols solution but it's not working, here is my query:
"SELECT
glpi_tickets.id,
glpi_tickets.name,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 1 THEN
CONCAT(glpi_users.firstname, ' ', glpi_users.realname)
END) AS creator,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 1 THEN
CONCAT(glpi_tickets_users.users_id)
END) AS creator_id,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 2 THEN
CONCAT(glpi_users.firstname, ' ', glpi_users.realname)
END) AS users,
GROUP_CONCAT(
CASE WHEN glpi_tickets_users.type = 2 THEN
CONCAT(glpi_tickets_users.users_id)
END) AS users_id,
glpi_tickets.date,
glpi_tickets.priority,
glpi_tickets.date_mod,
glpi_itilcategories.completename,
glpi_tickets.status,
glpi_tickets.users_id_lastupdater,
GROUP_CONCAT(
CASE WHEN glpi_tickets.users_id_lastupdater = glpi_users.id THEN
CONCAT(glpi_users.firstname, ' ', glpi_users.realname)
END SEPARATOR '<br>') AS last_updater,
glpi_tickets.content
FROM
glpi_tickets
JOIN glpi_tickets_users ON glpi_tickets_users.tickets_id = glpi_tickets.id
JOIN glpi_users ON glpi_users.id = glpi_tickets_users.users_id
JOIN glpi_itilcategories ON glpi_itilcategories.id = glpi_tickets.itilcategories_id
WHERE
exists (
SELECT
'x'
FROM
glpi_tickets_users
WHERE
glpi_tickets_users.tickets_id = glpi_tickets.id AND
glpi_tickets_users.id = ? AND
glpi_tickets_users.type = 1)
GROUP BY
glpi_tickets.id"
几乎始终没有显示任何票证，有时它显示的是票证但与用户无关，例如，如果我尝试users.php?id=1536，它将显示由用户870创建的票证号码789负责此票证的用户1180和1632 ...
Almost all the time it's showing no tickets at all, and sometimes it's showing a ticket but not related to the user, for example if I try users.php?id=1536 it will show the ticket number 789 that is created by the user 870 with user 1180 and 1632 in charge of this ticket...
推荐答案
您应该检查用户是否与此问题相关，而不是根据确切的用户ID进行过滤，如下所示:
Instead of filtering on an exact userid, you should check if the user is related to the issue, like this:
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
where
exists (
select
'x'
from
tickets_users tu2
where
tu2.ticketid = t.id and
tu2.userid = <youruserid> and
tu2.type = 1)
group by
t.id;
对于<youruserid>，您可以填写所需的用户ID.该查询将返回该用户报告的所有问题(类型= 1).但是对于所有这些问题，仍然会返回所有相关用户，因此您的查询结果仍然是完整的.
For <youruserid> you can fill in the user id you want. This query will return all issues that are reported by that user (type = 1). But for all those issues, still all related users are returned, so your query result is still complete.
这篇关于关于连接表再次需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
