参加两个具有foreg键的表的联接 [英] Take join in two tables having foregn key

问题描述

嗨！
我想用外键加入两个表，
第一个表是具有字段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.

推荐答案

我已经在这里回答了您的问题. 用外键联接在两个表上 [ ^ ]

为什么还要重新发布相同的问题呢?? ！！

I have answered your question here. join on two tables with foreign keys[^]

Why repost the same question again??!!

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

不能正常工作
我想要这样的结果..实际上我在这两个表中都有一个DateTime字段，并且评级和评论都在同一时间发布，所以我希望用户在同一时间在特定代理上发布cmment

no its not working
i want the result like this..actually i have a DateTime Field in both these tables and both rating and comment are posted at the same time so i want the cmment posted by a user on a specific agent on the same time

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键的表的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！