mysql select查询从同一用户名表调用名称的多个ID

发布于 2024-11-30 10:06:38 字数 593 浏览 0 评论 0原文

快问。感觉自己像个菜鸟，但还没有找到正确的语法。

有3张桌子。

T1- 评估ID（自动递增）检查员ID，助理ID, 植物名称

T2- 用户ID(自动递增), 用户名, 名称

T3- 植物ID（自动递增） PlantName

基本上我想要一个与 T1 非常相似的 php/mysql 表，但我不想列出 ID，而是想要名称。到目前为止，这是我的调用：

SELECT DISTINCT a.AssessmentID, u.Name, a.PlantAssistID, p.PlantName 
                FROM assessmentscores AS a, user AS u, plant AS p
                WHERE u.userID=a.InspectorID AND u.Name='$name' AND p.PlantID=a.Plant

这有效，因为 u.Name 将为我提供检查员的名称，但我不知道如何调用第二个（a.PlantAssistID）。只要继续为两者取相同的名字即可。

我应该使用什么调用服务器来返回两个不同的名称。任何帮助将不胜感激

原文

Quick question. feel like a noob but haven't found the right syntax for this yet.

Have 3 tables.

T1-
AsessessmentID(auto Incr)
inspectorID,
assistantID,
plant name

T2-
UserID(auto Incr),
username,
name

T3-
plantID(auto Incr)
PlantName

Basically I want to have a php/mysql table that is very similar to T1 but instead of listing the ID's I would like the names. Heres my call so far:

SELECT DISTINCT a.AssessmentID, u.Name, a.PlantAssistID, p.PlantName 
                FROM assessmentscores AS a, user AS u, plant AS p
                WHERE u.userID=a.InspectorID AND u.Name='$name' AND p.PlantID=a.Plant

This works as u.Name will give me the name for the inspector but I can't figure out how to call the second (a.PlantAssistID). Just keep getting the same name for both.

What call to the server should I use to return two different names. Any help would be appreciated

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

星軌x 2024-12-07 10:06:38

首先要做的事情是：如果您想从评估表中进行选择并使用其他表扩充结果集，请考虑使用 LEFT JOIN 。如果您想要更严格的结果（仅返回与现有用户实际关联的评估），请改用 INNER JOIN。下面的示例使用LEFT JOIN。

不确定这是否正是您想要的，但您可以尝试：

SELECT a.AssessmentID as a_id, u.Name as u_name, a.PlantAssistID as a_plantassistid, p.PlantName as p_plantname, uass.Name as u_ass_name
    FROM assessmentscores a
    LEFT JOIN user u ON u.UserID = a.AssessmentID
    LEFT JOIN plant p ON p.PlantID = a.Plant
    LEFT JOIN user uass ON uass.UserID = a.PlantAssistentID

结果：

array(
    'a_id'            => int    /* AssessmentID */
    'u_name'          => string /* User name */
    'a_plantassistid' => int    /* Assessment.PlantAssistID */
    'p_plantname'     => string /* Plant name */
    'u_ass_name'      => string /* The user name of the user with assistent_id */
);

First things first: if you want to select from the assessment table and augment the resultset using the other tables, think about using LEFT JOIN. If you want a more restrictive result, where only assessments are returned that are actually associated with existing users, use an INNER JOIN instead. The example below uses LEFT JOIN.

Not sure whether this is what you want exactly, but you can try:

SELECT a.AssessmentID as a_id, u.Name as u_name, a.PlantAssistID as a_plantassistid, p.PlantName as p_plantname, uass.Name as u_ass_name
    FROM assessmentscores a
    LEFT JOIN user u ON u.UserID = a.AssessmentID
    LEFT JOIN plant p ON p.PlantID = a.Plant
    LEFT JOIN user uass ON uass.UserID = a.PlantAssistentID

Result:

array(
    'a_id'            => int    /* AssessmentID */
    'u_name'          => string /* User name */
    'a_plantassistid' => int    /* Assessment.PlantAssistID */
    'p_plantname'     => string /* Plant name */
    'u_ass_name'      => string /* The user name of the user with assistent_id */
);

回复收藏 0 原文

情域 2024-12-07 10:06:38

我不是 100% 确定我明白你的意思，但这就是我认为你想要的：

我会重写查询以使用 JOIN，因为它可以更容易阅读

SELECT a.AssessmentID, u.Name, a.PlantAssistID, p.PlantName, u2.Name as AssisantName
FROM user AS u
    JOIN assessmentscores AS a ON u.UserID = a.InspectorID
    JOIN plant AS p ON p.plantID = a.Plant
    JOIN user as u2 ON u2.UserID = a.assistantId
WHERE u.Name='$name'

这里的关键是重新加入用户表，但给它一个不同的别名（此处为 u2）

I'm not 100% sure I follow you, but here's what I think you want:

I would rewrite the query to use JOIN, as the can be easier to read

SELECT a.AssessmentID, u.Name, a.PlantAssistID, p.PlantName, u2.Name as AssisantName
FROM user AS u
    JOIN assessmentscores AS a ON u.UserID = a.InspectorID
    JOIN plant AS p ON p.plantID = a.Plant
    JOIN user as u2 ON u2.UserID = a.assistantId
WHERE u.Name='$name'

The key here is to rejoin on the user table but to give it a different alias (u2 here)

回复收藏 0 原文

~没有更多了~