SQL Server - getting info from two tables

I'm having trouble creating a query which in my mind should be simple.

I have two tables (tblReviews and tblRating). Both these tables have a venueId and a userId.

I want to create a single开发者_Python百科 query that will return the review and the rating using the same venueId and userId. is this possible or should I use two queries?

Thanks in advance

SELECT Rev.column_name, Rat.column_name
  FROM dbo.tblReview AS Rev
  FULL OUTER JOIN dbo.tblRating AS Rat
  ON Rev.VenueId = Rat.VenueId
  AND Rev.UserId = Rat.UserId;

If you want all for a specific user:

SELECT Rev.column_name, Rat.column_name
  FROM dbo.tblReview AS Rev
  FULL OUTER JOIN dbo.tblRating AS Rat
  ON Rev.VenueId = Rat.VenueId
  AND Rev.UserId = Rat.UserId
  WHERE (Rev.UserId = @UserId OR Rat.UserId = @UserId);

If you want all for a specific venue:

SELECT Rev.column_name, Rat.column_name
  FROM dbo.tblReview AS Rev
  FULL OUTER JOIN dbo.tblRating AS Rat
  ON Rev.VenueId = Rat.VenueId
  AND Rev.UserId = Rat.UserId
  WHERE (Rev.VenueId = @VenueId OR Rat.VenueId = @VenueId);

You can

1. Join both tables using these fields;
2. ..or (SELECT first) UNION (SELECT second)

SELECT *
FROM tblReviews AS rev INNER JOIN
     tblRating AS rat ON rev.venueid = rat.venueid AND rev.userid = rat.userid

This query returns the matching rows from each tables. You can use outer joins (LEFT OUTER JOIN, RIGHT OUTER JOIN, also FULL OUTER JOIN) if you want all records from first, second, or both table.

I don't know if you're asking this.
Try:

SELECT re.venueId, re.userId, re.review, ra.rating
FROM tblReviews re INNER JOIN tblRating ra
ON re.venueId = ra.venueId AND re.userId = ra.userId