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
3
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
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
1
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
0
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
0
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
-1
Try:
试一试:
LEFT JOIN FoodType ft ON ft.FoodTypeID = cr.FoodTypeID WHERE
ft.FoodTypeID=1
Think could helps.
认为会有帮助。
#1
3
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
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
1
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
0
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
0
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
-1
Try:
试一试:
LEFT JOIN FoodType ft ON ft.FoodTypeID = cr.FoodTypeID WHERE
ft.FoodTypeID=1
Think could helps.
认为会有帮助。