在SQL连接中需要帮助

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.

我加入了3个表格，得到了错误的结果。目标是列出餐厅表中的所有餐厅，并显示任何餐厅的评级(如果存在评级的话)，否则显示空值，并且只显示汉堡的评级。

This is the SQL:

这是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:

这是由于:

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

但是，“Cafe C”应该具有= null的评级，因为我只想显示汉堡的评级。适当的SQL是什么?

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

创建表并使用数据填充的SQL语句:

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

6 个解决方案

#1

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'

6 个解决方案

#1

更多相关文章

随机推荐