参加两个具有foreg键的表的联接

问题描述

嗨！
我想用外键加入两个表，
第一个表是具有字段CommentID，userID(外键)，AgentID(外键)，Description(DateTime)的Comment
以及另一个带有字段RatingID，userID(外键)，AgentID(外键)，Rating(DateTime)的表Ratings

用户表是具有UserID作为主键的表，该表在注释和评级表中均用作外键，而AgentID是代理表中的主键在注释和评级表中均用作外键
现在我想查看的是同一用户在同一座席上即由AgentID给出的评论和评分.我正在基于用户ID加入评论和评分表，例如，我想查看给定同一用户的评分和评论
这是我正在使用的查询

hi!
i want to 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进行两个评级..它显示这样的记录

CommentID用户ID代理ID RATINGID说明评分
62 1 4 68最佳4
71 1 4 68你好4
62 1 4 8最佳2
71 1 4 8你好2

我想加入两个都具有外键的表.

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
62 1 4 68 best 4
71 1 4 68 hello 4
62 1 4 8 best 2
71 1 4 8 hello 2

i want to join on two tables both having foreign keys.

推荐答案

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.UserID=r.AgentID AND c.AgentID = 4

使用它可能会有所帮助

Use it might be helpful

62 1 4 68 best 4 4/22/2011 12:59:54 AM
71 1 4 8 hello 4 5/17/2011 2:22:03 PM

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