如何从3个表中的1个中找到丢失的输入 [英] How to find input missing from 1 of 3 tables

问题描述

我有3个表PostedRecipes，PostedRecipePictures和RecipeRatings。 PostedRecipes和PostedRecipePictures使用RecipeID作为密钥。 RecipeRatings使用RatingID作为键，但确实将RecipeID作为列名。



将所有3个表中的数据组合在一起，以显示带有图片和评级的食谱（PostedRecipes）。



PostedRecipes可能有数据，而其他两个没有任何数据。因此，食谱可能只是食谱，或带图片的食谱，或带图片和评级的食谱。



PostedRecipePictures和RecipeRatings都需要输入PostedRecipe，但是，如果在PostedRecipePictures或RecipeRatings上输入了RecipeID，则必须输入 - 两者都没有NULL。



我需要确定哪些PostedRecipes没有PostedRecipePictures，或RecipeRatings，或两者兼而有之。



我已经确定了哪些PostedRecipes有PostedRecipePictures但无法查询哪些PostedRecipes没有PostedRecipePictures或RecipeRatings。



任何指导将不胜感激。我尝试了以下查询。



我尝试了什么：



SELECT PostedRecipes.RecipeID，PostedRecipePictures.RecipeID AS Expr1

FROM PostedRecipes INNER JOIN

PostedRecipePictures ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID（给出共同ID的总数＃但不是缺少照片的地方）



SELECT PostedRecipes.RecipeID，PostedRecipePictures.RecipeID AS Expr1

FROM PostedRecipes INNER JOIN

PostedRecipePictures ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID AND PostedRecipes.RecipeID<> PostedRecipePictures.RecipeID（没有结果）



SELECT PostedRecipes.RecipeID，PostedRecipePictures.RecipeID AS Expr1

FROM PostedRecipes INNER JOIN

PostedRecipePictures ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID AND PostedRecipes.RecipeID = PostedRecipePictures.RecipeID（提供共同ID但不是我需要的总数）

解决方案

您需要左将PostedRecipes加入图片或评级，以获得您想要的结果。内部联接仅选择匹配的行，而左联接选择匹配的行，如果左侧的行在右侧没有任何匹配，则返回null。

 < span class =code-keyword> SELECT  PostedRecipes.RecipeID，PostedRecipePictures.RecipeID  AS  Expr1 
  FROM  PostedRecipes 
  LEFT   JOIN  PostedRecipePictures 
  ON  PostedRecipes.RecipeID = PostedRecipePictures.RecipeID 
  WHERE  PostedRecipePictures.RecipeID  IS   NULL  

或者您可以使用NOT EXISTS重写查询

  SELECT  PostedRecipes.RecipeID 
  FROM  PostedRecipes 
  WHERE   NOT   EXISTS （
  SELECT  *  FROM  PostedRecipePictures 
  WHERE  PostedRecipePictures.RecipeID = PostedRecipes.RecipeID 
） 

I have 3 tables PostedRecipes, PostedRecipePictures, and RecipeRatings. PostedRecipes and PostedRecipePictures use RecipeID as the key. RecipeRatings uses RatingID as key but does have RecipeID as a column name.

Data from all 3 tables are combined to present a recipe (PostedRecipes) with a picture and rating.

PostedRecipes may have data without the other two having any data. Therefore, a recipe could be just a recipe, or a recipe with picture, or a recipe with picture and rating.

PostedRecipePictures nor RecipeRatings are required to input a PostedRecipe, however, if a RecipeID is entered on either PostedRecipePictures or RecipeRatings input must be made - there is no NULL on either.

I need to identify which PostedRecipes do not have a PostedRecipePictures, or a RecipeRatings, or both.

I have identified which PostedRecipes have PostedRecipePictures but cannot query which PostedRecipes do not have PostedRecipePictures or RecipeRatings.

Any guidance would be appreciated. I have tried the following queries.

What I have tried:

SELECT PostedRecipes.RecipeID, PostedRecipePictures.RecipeID AS Expr1
FROM PostedRecipes INNER JOIN
PostedRecipePictures ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID (gives the total # of common IDs but not where pics are missing)

SELECT PostedRecipes.RecipeID, PostedRecipePictures.RecipeID AS Expr1
FROM PostedRecipes INNER JOIN
PostedRecipePictures ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID AND PostedRecipes.RecipeID <> PostedRecipePictures.RecipeID (which gives no results)

SELECT PostedRecipes.RecipeID, PostedRecipePictures.RecipeID AS Expr1
FROM PostedRecipes INNER JOIN
PostedRecipePictures ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID AND PostedRecipes.RecipeID = PostedRecipePictures.RecipeID (which give the total of common ID but not what I need)

解决方案

You need left join PostedRecipes to pictures or ratings in order to get the results you want. Inner join only selects the matching rows, while left join selects the matching rows or null if the row on the left doesn't have any match on the right.

SELECT PostedRecipes.RecipeID, PostedRecipePictures.RecipeID AS Expr1
FROM PostedRecipes 
LEFT JOIN PostedRecipePictures
    ON PostedRecipes.RecipeID = PostedRecipePictures.RecipeID
WHERE PostedRecipePictures.RecipeID IS NULL

Alternatively you can rewrite the query using NOT EXISTS

SELECT PostedRecipes.RecipeID
FROM PostedRecipes 
WHERE NOT EXISTS (
    SELECT * FROM PostedRecipePictures 
    WHERE PostedRecipePictures.RecipeID = PostedRecipes.RecipeID
)

这篇关于如何从3个表中的1个中找到丢失的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！