用deferend命名获取结果两次 [英] Fetching a result twice with deferend name

问题描述

我有1个名为opentask的表格：
列：id，title，description，expires，creator_id，creator_name，executer_id，executer_name，priority_id，status_id用户：_
与列：user_id，用户名
我想要的是创建一个查询，其中将有来自opentask表的所有列，其中executer_id将等于user_id的用户表，并且creator_id将再次等于user_id。这会造成混乱，因为第一个等式排除了第二个。

我需要以某种方式创建一个查询，我将在其中包含用户名，其中包含opentask.executer_id = user_user_id和在同一时间，我将再次使用用户名（作为一个不同的名字？）为创建者提供类似where opentask.executer_id = user_user_id的例子，所以我尝试这个，我当然知道这是缺少一些东西，你可以帮忙吗？

I have 1 table with tasks named opentask:
columns: id,title,description,expires,creator_id,creator_name, executer_id, executer_name, priority_id, status_id
1 table with users named user:
with columns: user_id, username
What I want is to create a query where there will be all columns from opentask table where the executer_id will be equal to the user_id of user table AND the creator_id will be equal to the user_id again. This creates a confusion because the first equality excludes the second.

So I need somehow to create a query where I will include the usernames for the executer with something like where "opentask.executer_id=user_user_id" and at the same time I will include the username again (as a differend name?) for the creator with something like "where opentask.executer_id=user_user_id"

So I try this, which of course I know that is missing something, can you help?

SELECT DISTINCT id，title，description，expires，creator_id，executer_id，oc_opentask.priority_id，oc_opentask.status_id ，priority_name，status_name，user_id，username，（SELECT username FROM oc_opentask，oc_user WHERE oc_opentask.creator_id = oc_user.user_id）AS username2 FROM oc_opentask，oc_opentask_priority，oc_user，oc_opentask_status WHERE oc_opentask.priority_id = oc_opentask_priority.priority_id AND oc_opentask.executer_id = oc_user。 user_id AND oc_opentask.status_id = oc_opentask_st atus.status_id ORDER BY oc_opentask.expires DESC

SELECT DISTINCT id, title, description, expires, creator_id, executer_id, oc_opentask.priority_id, oc_opentask.status_id, priority_name, status_name, user_id, username, (SELECT username FROM oc_opentask, oc_user WHERE oc_opentask.creator_id=oc_user.user_id) AS username2 FROM oc_opentask, oc_opentask_priority, oc_user, oc_opentask_status WHERE oc_opentask.priority_id=oc_opentask_priority.priority_id AND oc_opentask.executer_id=oc_user.user_id AND oc_opentask.status_id=oc_opentask_status.status_id ORDER BY oc_opentask.expires DESC

推荐答案

Re JOIN 表 oc_user 两次使用不同的别名创作者和执行者像这样：

ReJOIN the table oc_user twice with different aliases creators and executers like so:

SELECT DISTINCT 
  t.*, 
  creators.user_name 'Creator name',
  executers.username 'Executor name', 
  tp.priority_name, 
  s.status_name
FROM oc_opentask t 
INNER JOIN oc_opentask_priority tp        ON t.priority_id = tp.priority_id
INNER JOIN oc_user              executers ON t.executer_id = executes.user_id
INNER JOIN oc_user              creators  ON t.creator_id  = creators.user_id
INNER JOIN oc_opentask_status   s         ON t.status_id   = s.status_id
ORDER BY t.expires DESC

这篇关于用deferend命名获取结果两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！