doujianchao7446 2014-12-14 13:34
浏览 58
已采纳

在SQL Query中加入3个或更多表,获取空数组?

Ok so after going over this multiple times I don't understand what I am missing

I have at the moment 3 tables:

Users Table:

ID | Username | Password
1  |   Micky  |   123
2  |   Mouse  |   145

Questions Table:

 Question ID| Question_Title   | Question      | Rating | Category ID | User ID |
     1      | Meaning of Life? | Same as Title |  100   |      2      |    1    |
     2      | Foo is love?     | Same as Above |   95   |      4      |    2    |

Answer Table:

   Answer_ID| Answer   | Answer_Rating     | Answer_User_ID    | Question_ID |
     1      | YES      |       1           |     1             |      1      | 

And I have the following Query:

"SELECT u.username, 
q.Question_Title, 
q.Question, 
q.Rating, 
a.Answer,
a.Answer_Rating
FROM Questions q 
join Users u on u.id = q.User_ID
join Answers a on a.Question_ID = 'q.Question ID' 

WHERE q.Question_Title LIKE '%$Term%'";

This will get me the Username of the person who asked the question in addition to the Question Title, Question, Question Rating, Answer to the question and the Rating of the answer.

However what do i add to the query so that it will print out the username of the person who answered the question, via the foreign key Answer_User_ID?

No matter what I have tried, It will turn out empty, it will return the query in associative array and I am currently using print_r to display the array.

P.S: The table structure will grow and I would like to know if their is a consistent way of adding to the query to include fields and multiple foreign keys that will be added later.

  • 写回答

1条回答 默认 最新

  • dtc99987 2014-12-14 13:36
    关注

    You need another join to users:

    SELECT u.username, q.Question_Title, q.Question, q.Rating, 
           a.Answer, a.Answer_Rating,
           ua.username as AnswerUserName
    FROM Questions q join
         Users u
         on u.id = q.User_ID join
         Answers a
         on a.Question_ID = q.`Question ID` join
         Users ua
         on ua.id = a.Answer_User_ID
    WHERE q.Question_Title LIKE '%$Term%'";
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 画两个图 python或R
  • ¥15 在线请求openmv与pixhawk 实现实时目标跟踪的具体通讯方法
  • ¥15 八路抢答器设计出现故障
  • ¥15 请教一下c语言的代码里有一个地方不懂
  • ¥15 opencv 无法读取视频
  • ¥15 用matlab 实现通信仿真
  • ¥15 按键修改电子时钟,C51单片机
  • ¥60 Java中实现如何实现张量类,并用于图像处理(不运用其他科学计算库和图像处理库))
  • ¥20 5037端口被adb自己占了
  • ¥15 python:excel数据写入多个对应word文档