当field = 0时，MySQL返回所有行 [英] MySQL Returns All Rows When field = 0

问题描述

我在这里看到了一些与此非常相似的问题，但是我没有找到我认为是我的问题的答案.

I've seen a few questions on here very similar to this however I have not found an answer specific to what I believe my problem to be.

我有两个表，subcompanies和presets.这两个表通过ID CntRefID和PresetIDFoxPro链接在一起.我想在ID的匹配时从Preset表的文本列中选择所有文本.

I have two tables, subcompanies and presets. The two tables are linked together through an ID, CntRefID and PresetIDFoxPro. I want to select all of the text from the Preset table's text column when the ID's matchup.

当CntRefID中包含数字时，此功能可正常使用.但是，如果它为0，则将返回presets文本列中的每个字段.

This works flawlessly when the CntRefID has a number in it. However, if it is 0 then every single field from the presets text column is returned instead.

这是我的查询文字；

myQuery.CommandText =   
"SELECT CompanyID, CompanyName, PresetText, InvHeader, Prefix, NextBnum  
FROM sdcdatabase.sdcsubcompanies, sdcdatabase.presets   
WHERE (CntRef=PresetIDFoxPro OR CntRef='0') AND PresetReferenceFoxPro=3";

我无法确定为什么选择了每个字段.我已经尝试在引号和不引号中使用0，我也已经看到它可能是由于一个字段是整数，而一个字段是Char(

I cannot get my head round why every field is selected. I have tried 0 in quotes and without quotes, I have also seen it could be due to one field being an Integer and one being a Char (mySQL returns all rows when field=0). Both my fields are integers however.

推荐答案

由于OR CntRef='0'条件(其中CntRef为0)，返回了presets表中的所有行.

All rows from the presets table are returned due to the OR CntRef='0' condition (where CntRef is 0).

这是因为您现有的where子句可以解释为(忽略PresetReferenceFoxPro条件)为:

This is because your existing where clause can be paraphrased (ignoring the PresetReferenceFoxPro condition) as:

我想要subcompanies表中的所有记录以及presets表中的任何匹配记录，或者presets表中CntRef为0的presets表中的所有记录.

I want all records from the subcompanies table and either any matching records from the presets table, or all records from the presets table where CntRef is 0 on the subcompanies table.

如果即使在预设上没有匹配项时也要返回subcompanies记录，则需要使用LEFT OUTER JOIN-像这样:

If you want to return subcompanies records even when they don't have a match on presets, then you need to use a LEFT OUTER JOIN - like so:

SELECT CompanyID, CompanyName, PresetText, InvHeader, Prefix, NextBnum 
FROM sdcdatabase.sdcsubcompanies as s 
LEFT OUTER JOIN sdcdatabase.presets as p 
ON s.CntRef=p.PresetIDFoxPro AND p.PresetReferenceFoxPro=3

我假设PresetReferenceFoxPro在presets表上-它将澄清查询以向SELECT子句中的每个列添加表别名，但是我不知道哪些列来自哪些表.

I'm assuming that PresetReferenceFoxPro is on the presets table - it would clarify the query to add table aliases to each of the columns in the SELECT clause, but I don't know which columns come from which tables.

这篇关于当field = 0时，MySQL返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！