join in two tables with foreign keys.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join in two tables with foreign keys.

Author

Topic

honee
Starting Member

5 Posts

Posted - 2011-05-16 : 14:36:20

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
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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-16 : 20:49:49

So how would you like to have the result if not as its appearing. Do you want the query should return one row (rating and comments) per agent? If so, what would be the criteria for returning information? (what should be the desired output as per above displayed output)

Cheers
MIK

honee
Starting Member

5 Posts

Posted - 2011-05-17 : 06:02:21

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-17 : 13:25:07

It seems that you want to get the highest rating in case of duplicate?

If so may be following is what you are looking after

Select Col1,Col2,Col3,...,ColN,MAX(ColX) As Rating
From Comments C
Inner Join Ratings R on C.userID=R.userID and C.AgentID=R.AgentID
Inner Join Users U On U.UserID=R.UserID
Where C.AgentID=4
Group by Col1,Col2,Col3,...,ColN

You will need to replace Col1 to ColN in the Select and Group by clauses with the required ones.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources