mysql多个INNER JOIN将两个表列添加到另一个表中

three tables

status : ID,member_1,member_2 -->

(eg. 12, member_Joseph, member_John ; 
     22, member_Jacob, member_Jarrod ; 
     31, member_Jarrod, member_John 
     11, member_John, member_Jacob)

submit : ID,student -->

(eg. 12, Amy ; 
     22, Brian; 
     31 Susan ; 
     11 Kyle) 

info : member_name, contact -->

(eg. member_Joseph, joseph@name.name; 
     member_Jacob, jacob@name.name ; 
     member_Jarrod, jarrod@name.name ; 
     member_John, john@name.name )

New table to build from query:

 member_Joseph, joseph@name.name, Amy; 
 member_Jacob, jacob@name.name, Kyle ; 
 member_Jarrod, jarrod@name.name, Brian, Susan ; 
 member_John, john@name.name, Amy, Susan, Kyle )

I'd like to make arrays of all students assigned to each member in status table
eg.
array1 --> member_Joseph: student 1, student 2, student 3
array2 --> member_John: student 2, student 4, student 5
array3 -->member_Jacob: student 3, student 5, student 1

Would that be:

$query = db_query("
    SELECT info.member_name, submit.student  
    FROM {info} 
    INNER JOIN {status} 
    ON info.member_name = status.member_1
    INNER JOIN {status}
    ON info.member_name = status.member_2
    INNER JOIN {submit}
    ON submit.id = status.id
");
while($result = db_fetch_array($query)){
echo $result['student']; //iterate through members and print students for each
}

The error is: "Not unique table/alias"

(fyi: I'm using Drupal 6 syntax)

dongtan7998
dongtan7998 ic,我不知道别名到现在为止。谢谢
7 年多之前 回复
dtpw54085
dtpw54085 问题是,我需要查看状态两次以获取分配的第一个和第二个成员的名称
7 年多之前 回复
douxiluan6555
douxiluan6555 我认为{status}被解析为同一个别名两次,导致错误。
7 年多之前 回复

1个回答



我认为应该将表 info </ code>连接两次,这样你就可以得到每个memberID的member_name 。 您还需要为表提供别名,以便可以唯一标识它们,例如</ p>

  SELECT a。*,
b.member_name memberName1,
c.member_name memberName2 ,
//您可能希望显示的其他列
FROM状态a
INNER JOIN信息b
ON a.member_1 = b.member_name
INNER JOIN info c
ON a.member_2 = c.member_name
INNER JOIN提交d
ON a.ID = d.ID
</ code> </ pre>

要进一步了解联接,请访问以下链接:</ p> \ n

展开原文

原文

I think it should be the table info should be joined twice so you can get the member_name of each memberID. You also need to supply an alias for the table so they can be uniquely identified, eg

SELECT  a.*, 
        b.member_name memberName1,
        c.member_name memberName2,
        // other columns you might want to show
FROM    status a        
        INNER JOIN info b
            ON a.member_1 = b.member_name
        INNER JOIN info c
            ON a.member_2 = c.member_name
        INNER JOIN submit d
            ON a.ID = d.ID

To further gain more knowledge about joins, kindly visit the link below:

douyaosi3164
douyaosi3164 所以我这样做了。 重组表可能是有意义的,这样我就有一个成员名称为列的表,然后从上到下填写学生。 然后我不需要这样做。 这没有意义,因为行不相关
7 年多之前 回复
dozabg1616
dozabg1616 你可以添加样本记录和你想要得到的结果:D所以我们可以解决它。
7 年多之前 回复
duanlu0386
duanlu0386 你有什么问题? :D或查询帮助你吗?
7 年多之前 回复
doudang4568
doudang4568 我试图说明ID只是表示提交#,而member_1和member_2是包含分配给提交#的成员的列。 谢谢你的链接!
7 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐