CODEWITHSUNDEEP
sqlsql-server
Dan
Dan

Reputation: 204

How to rollup multiple rows when Inner Joining two tables

I am stuck with an SQL command where I am joinig two tables. I have two tables: a 'restaurant' table and a 'review' table. The relationship is many reviews to one restaurant. After joining the tables I have many reviews for each restaurant, hence I end up with many duplicates for each restaurant. All I need is to group the duplicate restaurant rows in order to output a restaurant one one time in the search results I have implemented. The code so far is:

SELECT a.Restaurant_Id, a.Restaurant_Name, a.Restaurant_Location, 
   a.Restaurant_Image, a.Restaurant_Thumbnail, a.Restaurant_Owner, a.Restaurant_Description
FROM restaurant a INNER JOIN review b 
  ON a.Restaurant_Id=b.Restaurant_Id

What is the simplest way of grouping the duplicate restaurant entries (I don't need to output any data from the 'review' table, I have only joined it to the 'restaurant' table for the search function on my website)?

I need the column data from the 'restaurant' table (as defined in the select statement above) in order to output the restaurant information on my website (as a list of restaurants). e.g.

Id  Name           Location    Image   Thumbnail   Owner   Description
-------------------------------------------------------------------------------  
10  Red Lion       Manchester                       8       A description...

Thanks

Upvotes: 0

Views: 1166

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9149

EDIT: As noted in the comments, it turns out the table contains IMAGE data and is therefore not comparable. That means GROUP BY and DISTINCT will both fail. I'll leave this up as it might make that but obvious to someone else at some point.

You could group by all the field you want to return.

SELECT a.Restaurant_Id, a.Restaurant_Name, a.Restaurant_Location, 
   a.Restaurant_Image, a.Restaurant_Thumbnail, a.Restaurant_Owner, a.Restaurant_Description
FROM restaurant a INNER JOIN review b 
  ON a.Restaurant_Id=b.Restaurant_Id
GROUP BY a.Restaurant_Id, a.Restaurant_Name, a.Restaurant_Location, 
   a.Restaurant_Image, a.Restaurant_Thumbnail, a.Restaurant_Owner, a.Restaurant_Description

Which is the same as doing a DISTINCT on them but faster.

SELECT DISTINCT a.Restaurant_Id, a.Restaurant_Name, a.Restaurant_Location, 
   a.Restaurant_Image, a.Restaurant_Thumbnail, a.Restaurant_Owner, a.Restaurant_Description
FROM restaurant a INNER JOIN review b 
  ON a.Restaurant_Id=b.Restaurant_Id

But, really, if you can avoid the the join you are better off.

Upvotes: 0

Joseph B
Joseph B

Reputation: 5669

It appears that you just want to ensure that there is at least one review for a restaurant for it to be listed. You can either using a LEFT OUTER JOIN or WHERE EXISTS to accomplish this requirement.

LEFT OUTER JOIN:

SELECT DISTINCT a.Restaurant_Id, a.Restaurant_Name, a.Restaurant_Location, 
   a.Restaurant_Image, a.Restaurant_Thumbnail, a.Restaurant_Owner, a.Restaurant_Description
FROM restaurant a LEFT OUTER JOIN review b 
  ON a.Restaurant_Id=b.Restaurant_Id
WHERE b.Restaurant_Id IS NOT NULL;

WHERE EXISTS:

SELECT a.Restaurant_Id, a.Restaurant_Name, a.Restaurant_Location, 
   a.Restaurant_Image, a.Restaurant_Thumbnail, a.Restaurant_Owner, a.Restaurant_Description
FROM restaurant a 
WHERE EXISTS
(SELECT b.Restaurant_Id
FROM review b 
WHERE a.Restaurant_Id=b.Restaurant_Id);

Upvotes: 1

Related Questions