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