vb.net 左连接包括 where 条件不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24557795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left join including a where condition not working
提问by user2076963
I'm going nuts. It seems everyone has had this same problem by the number of results I get when I google ‘left join not working'. I've studied them all and despite best efforts I cannot get my specific problem to work. Please help.
我快疯了 似乎每个人都遇到了同样的问题，因为我在谷歌“左连接不起作用”时得到的结果数量。我已经研究了所有这些，尽管尽了最大努力，我还是无法解决我的具体问题。请帮忙。
I have two tables; an animals_Table and an animalMilestones_Table.
我有两张桌子；一个animals_Table 和一个animalMilestones_Table。
Some of the animals in the animals_Table have intake records in the animalMilestones_Table and some do not. I want to join the two tables on animalID and show all rows in the animals_Table including those that have no matching ‘Intake' record in the animalMilestones_Table.
在animals_Table 中的一些动物在animalMilestones_Table 中有摄入记录，而有些则没有。我想加入animalID 上的两个表并显示animals_Table 中的所有行，包括那些在animalMilestones_Table 中没有匹配“摄入”记录的行。
Here are the two things I've tried that I thought would give the best chance for success but alas, neither work.
以下是我尝试过的两件事，我认为它们会给成功的最佳机会，但可惜，两者都不起作用。
SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN animalMilestones_Table ON animals_Table.animalID=animalMilestones_Table.animalID
WHERE animalMilestones_Table.milestoneType="Intake"
SELECT animalID
FROM animals_Table
LEFT JOIN (SELECT milestoneType
FROM animalMilestones_Table
WHERE animalMilestones_Table.milestoneType = "Intake")
ON animals_Table.animalID = animalMilestones_Table.animalID
采纳答案by Gord Thompson
It seems to me that you want a list of all [animalID] values from [animals_Table] along with a column that indicates whether or not that [animalID] has a "milestone" equal to 'Intake'. If so, then I would suggest this:
在我看来，您想要一个包含 [animals_Table] 中所有 [animalID] 值的列表以及一个列，该列指示该 [animalID] 是否具有等于“摄入量”的“里程碑”。如果是这样，那么我建议这样做：
SELECT at.animalID, amt.milestoneType
FROM
animals_Table at
INNER JOIN
animalMilestones_Table amt
ON at.animalID = amt.animalID
WHERE amt.milestoneType = 'Intake'
UNION
SELECT animalID, NULL AS milestoneType
FROM animals_Table
WHERE animalID NOT IN (
SELECT animalID
FROM animalMilestones_Table
WHERE milestoneType = 'Intake'
)
ORDER BY 1
Come to think of it, this should work too, and may prove to be faster:
想想看，这也应该有效，并且可能会更快：
SELECT at.animalID, amt.milestoneType
FROM
animals_Table at
LEFT JOIN
(
SELECT animalID, milestoneType
FROM animalMilestones_Table
WHERE milestoneType = 'Intake'
) amt
ON at.animalID = amt.animalID
回答by Ruslan
Not sure if MS Access supports this, but in SQL you would move your condition from the WHEREclause into the LEFT JOIN, like this:
不确定 MS Access 是否支持这一点，但在 SQL 中，您可以将条件从WHERE子句移动到 中LEFT JOIN，如下所示：
SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN animalMilestones_Table ON animals_Table.animalID=animalMilestones_Table.animalID AND animalMilestones_Table.milestoneType="Intake"
回答by MrMauricioLeite
maybe the code below work for you.
也许下面的代码适合你。
SELECT
*
FROM
animals_table at
LEFT JOIN animalmilestones_table amt ON at.animalID = amt.animalID AND amt.milestoneType = "Intake"
Please, try it out and tell me how it goes.
请尝试一下，然后告诉我进展如何。
回答by Sid M
Try this
尝试这个
SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN (SELECT milestoneType
FROM animalMilestones_Table
WHERE milestoneType = "Intake") as animalMilestones_Table
ON animals_Table.animalID = animalMilestones_Table.animalID;
回答by Lee Willis
animalMilestones_Table.milestoneType will be NULL in cases where there is no record. You need to check for it being NULL or the value you are querying for.
在没有记录的情况下，animalMilestones_Table.milestoneType 将为 NULL。您需要检查它是否为 NULL 或您要查询的值。
animalMilestones_Table.milestoneType="Intake" or animalMilestones_Table.milestoneType is null
Not sure on the exact SQL syntax in Accesss
不确定 Accesss 中的确切 SQL 语法
回答by Access Guru
try out the following query. hope this may help you and let me know if this helps..
尝试以下查询。希望这可以帮助你，如果这有帮助，请告诉我..
SELECT animals_Table.AnimalID, animalMilestones_Table.MileStoneType
FROM animals_Table LEFT JOIN animalMilestones_Table ON animals_Table.AnimalID = animalMilestones_Table.AnimalID
WHERE ((((animalMilestones_Table.MileStoneType)='Intake' Or (animalMilestones_Table.MileStoneType) like '*') Or (animalMilestones_Table.MileStoneType) Is Null)) ;
回答by abalos
Does:
做：
SELECT animals_Table.animalID, animalMilestones_Table.milestoneType
FROM animals_Table
LEFT JOIN animalMilestones_Table ON animals_Table.animalID=animalMilestones_Table.animalID
AND animalMilestones_Table.milestoneType="Intake"
Work?
工作？
