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

#2

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

#3

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'

#4

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

您在编写外部连接时遇到了一些问题，所以这里有一种替代方法可以避免它们(以及null值，外部连接明确设计为返回:)

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

#5

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

像这样的东西应该有用;在真正加入之前，您需要删除“TACO”行

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

也许语法不完全正确，因为我没有SQL-Server来测试它，但是想法应该是这样的，就像这样，它可以在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"

#6

Try:

试一试:

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

Think could helps.

认为会有帮助。