用外键联接两个表 [英] join on two tables with foreign keys

问题描述

嗨！
我想在两个带有外键的表上进行联接，
第一个表是具有字段CommentID，userID(外键)，AgentID(外键)，Description(DateTime)的Comment
和
另一个表带有字段RatingID，userID(外键)，AgentID(外键)，Rating(DateTime)的Ratings

用户表是具有UserID作为主键的表，在注释表和评级表中都用作外键.
并且AgentID是Agent表中的主键，用作注释和评级表中的外键
现在我想要的是查看同一用户在同一Agent i-e AgentID上给出的评论和评级.

我正在基于用户ID加入评论和评分表，例如，我想查看给定同一用户的评分和评论
这是我正在使用的查询

hi!
i want to take join on two tables with foreign keys,
1st table is Comments having fields CommentID,userID(foreign key),AgentID(foreign Key),Description(DateTime)
and
another table Ratings with fields RatingID,userID(foreign key),AgentID(foreign Key),Rating(DateTime)

User Table is the table having UserID as a primary key which is used as foreign keys in both comment and rating table.
and AgentID is the Primary key in Agent Table used as foreign key in bothe comment and ratings table
now wot i want is to see the comments and rating given by the same user on a same Agent i-e AgentID .

i am taking join on Comment and Ratings table based on User ID for e.g i want to see rating and comment of the given same user
here is the query i am using

SELECT c.CommentID, c.UserID, c.PropertyID, c.PropertyType, c.DateTIme, c.Description, c.AgentID, c.visibility, r.RatingID, r.UserID AS Expr1, r.PropertyID AS Expr2, 
r.DateTime AS Expr3, r.AgentID AS Expr4, r.Rating
FROM Comments AS c INNER JOIN
Ratings AS r ON c.UserID = r.UserID AND c.AgentID = r.AgentID AND c.AgentID = 4

但是问题是它采用了交叉连接.我对一个代理有两个注释，并且对由同一userID给出的同一agentID = 4进行两个评级..它显示这样的记录

but the problem is it is taking cross join.i have two comments on a agent and two rating on the same agentID=4 given by the same userID..it displays record like this

CommentID UserID AgentID RAtingID Description Rating<br />
62      1 4 68 best 4<br />
71 1 4 68 hello 4<br />
62 1 4 8 best 2<br />
71 1 4 8 hello 2

推荐答案

如果要通过用户ID加入，

If you want to join by User Id then

FROM Comments AS c INNER JOIN
Ratings AS r ON c.UserID = r.UserID  WHERE c.AgentID = 4 AND r.AgentID=4

这个c.AgentID = r.AgentID AND c.AgentID = 4 A = B和A = 5，意味着A = 5和B = 5，不是吗. :)

This one c.AgentID = r.AgentID AND c.AgentID = 4 A=B and A=5, means A=5 and B=5, Isn''t it. :)

这篇关于用外键联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！