CODEWITHSUNDEEP
sqlsql-server
Tony_Henrich
Tony_Henrich

Reputation: 44225

Need help in a SQL join

I am joining between 3 tables and getting the wrong result. The goal is to list ALL the restaurants in the restaurants table and display the rating for any restaurant if the rating exists, otherwise display null, and only for ratings for burger.

This is the SQL: 

    SELECT r.RestaurantID, RestaurantName, cr.Rating FROM Restaurant r
      LEFT JOIN CustRating cr ON cr.RestaurantID = r.RestaurantID
      LEFT JOIN FoodType ft ON ft.FoodTypeID = cr.FoodTypeID AND 
        ft.FoodTypeName = 'Burger'

This is the result:

enter image description here

However 'Cafe C' should have Rating = null because I only want to display ratings for Burgers. What's the proper SQL?

The SQL Statements to create the tables and populate with data:

CREATE TABLE [dbo].[Restaurant](
    [RestaurantID] [int]  NOT NULL,
    [RestaurantName] [varchar](250) NOT NULL

)

CREATE TABLE [dbo].[FoodType](
    [FoodTypeID] [int]  NOT NULL,
    [FoodTypeName] [varchar](50) NOT NULL

)
CREATE TABLE [dbo].[CustRating](
    [RestaurantID] [int] NOT NULL,
    [FoodTypeID] [int] NOT NULL,
    [Rating] [smallint] NOT NULL
 ) 

 BEGIN TRANSACTION;
INSERT INTO [dbo].[CustRating]([RestaurantID], [FoodTypeID], [Rating])
SELECT 2, 1, 3 UNION ALL
SELECT 3, 2, 2
COMMIT;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[FoodType]([FoodTypeID], [FoodTypeName])
SELECT 1, N'Burger' UNION ALL
SELECT 2, N'Taco'
COMMIT;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[Restaurant]([RestaurantID], [RestaurantName])
SELECT 1, N'Cafe A' UNION ALL
SELECT 2, N'Cafe B' UNION ALL
SELECT 3, N'Cafe C'
COMMIT;
GO

Upvotes: 2

Views: 130

Answers (6)

Hons
Hons

Reputation: 4056

Something like this should work; You need to remove the "TACO"-row before actually join with it

SELECT r.RestaurantID, RestaurantName, crft.Rating 
   FROM Restaurant r    
        LEFT JOIN (SELECT cr.RestaurantID, cr.Rating
                     FROM CustRating cr JOIN FoodType ft  
                       ON ft.FoodTypeID = cr.FoodTypeID 
                    WHERE ft.FoodTypeName = 'Burger' ) as crft 
        ON crft.RestaurantID = r.RestaurantID

Maybe the Syntax is not completely right because I have no SQL-Server to test it, but the idea should be this, and like this it works perfektly on PostgreSQL

SELECT "r"."RestaurantID", "RestaurantName", "crft"."Rating" 
   FROM "Restaurant" r    
        LEFT JOIN (SELECT * 
                     FROM "CustRating" cr JOIN "FoodType" ft  
                       ON "ft"."FoodTypeID" = "cr"."FoodTypeID"
                    WHERE "ft"."FoodTypeName" = 'Burger' ) as crft 
        ON "crft"."RestaurantID" = "r"."RestaurantID"

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21776

Use this query:

SELECT DISTINCT
  r.RestaurantID, 
  RestaurantName, 
  CASE WHEN ft.FoodTypeID IS NULL THEN NULL ELSE cr.Rating END Rating
FROM Restaurant r
LEFT JOIN CustRating cr ON cr.RestaurantID = r.RestaurantID
LEFT JOIN FoodType ft ON ft.FoodTypeID = cr.FoodTypeID AND 
ft.FoodTypeName = 'Burger'

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57093

You are having problems writing an outer join so here's An alternative approach that avoids them (and the null value, which outer join is expressly designed to return :)

SELECT DISTINCT r.RestaurantID, r.RestaurantName, 
       N'Burger' AS FoodTypeName, cr.Rating
  FROM dbo.Restaurant r
       JOIN dbo.CustRating cr 
          ON r.RestaurantID = cr.RestaurantID
       JOIN dbo.FoodType ft
          ON ft.FoodTypeID = cr.FoodTypeID
             AND ft.FoodTypeName = N'Burger'
UNION
SELECT DISTINCT r.RestaurantID, r.RestaurantName, 
       N'Burger' AS FoodTypeName, -1 AS Rating
  FROM dbo.Restaurant r
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM dbo.CustRating cr 
                          JOIN dbo.FoodType ft
                             ON ft.FoodTypeID = cr.FoodTypeID
                                AND ft.FoodTypeName = N'Burger'
                    WHERE r.RestaurantID = cr.RestaurantID
                   );

Upvotes: 0

Renjith Kumar
Renjith Kumar

Reputation: 149

SELECT              
                    Restaurant.*
                    ,CASE  WHEN FoodType.FoodTypeID IS NULL THEN NULL ELSE CustRating.Rating END AS Rating

 FROM               Restaurant Restaurant
 LEFT OUTER JOIN    CustRating CustRating 
 ON                 Restaurant.RestaurantID =   CustRating.RestaurantID
 LEFT OUTER JOIN    FoodType FoodType 
 ON                 FoodType.FoodTypeID = CustRating.FoodTypeID
 AND                FoodType.FoodTypeName = 'Burger'

Upvotes: 3

Kamil Będkowski
Kamil Będkowski

Reputation: 1092

Try:

  LEFT JOIN FoodType ft ON ft.FoodTypeID = cr.FoodTypeID WHERE 
    ft.FoodTypeID=1

Think could helps.

Upvotes: -1

Steve B
Steve B

Reputation: 37720

try :

SELECT r.RestaurantID, RestaurantName, T.Rating 
FROM Restaurant r
  LEFT JOIN (
    select cr.RestaurantID, cr.Rating 
    from FoodType f 
    inner join CustRating cr on f.FoodTypeID = cr.FoodTypeID
    where f.FoodTypeName = 'Burger'
  ) T on r.RestaurantID = t.RestaurantID

Upvotes: 2

Related Questions