CODEWITHSUNDEEP
sqlsql-server
twsJames
twsJames

Reputation: 425

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

Upvotes: 0

Views: 110

Answers (4)

anon
anon

Reputation: 

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

Upvotes: 5

Marco
Marco

Reputation: 57573

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

Upvotes: 0

luviktor
luviktor

Reputation: 2270

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.

Upvotes: 0

Gedrox
Gedrox

Reputation: 3612

You can

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

Upvotes: 0

Related Questions