Click here to Skip to main content
15,887,485 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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

SQL
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


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.
Posted
Updated 16-May-11 9:04am
v2
Comments
Karthik. A 16-May-11 15:04pm    
Formatted for readability
Mohd Wasif 17-May-11 0:59am    
Hey Wat do u want if am not wrong you want the result should be first two lines means

62 1 4 68 best 4
71 1 4 68 hello 4
Mohd Wasif 17-May-11 1:01am    
Please provide the result you want
hinzhonee 17-May-11 6:01am    
no 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

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

Why repost the same question again??!!
 
Share this answer
 
SQL
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
 
Share this answer
 
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
 
Share this answer
 
v2
Comments
ZeeroC00l 17-May-11 6:07am    
-- Edited the pre tag. Hope you don't mind
ZeeroC00l 17-May-11 6:09am    
post Your comments or clarification as a reply to the Answers provided. Don't post them as answers, otherwise you can just improve your question with additional details.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900