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.

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.

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.

This is my query text;

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

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.

Community
  • 1
  • 1
CBreeze
  • 2,925
  • 4
  • 38
  • 93
  • Why does your query include the `OR CntRef='0'` condition? Is it because you know there are no matching `presets` records with that ID, and you want to return `subcompanies` records even when they don't have a match on `presets` ? –  Nov 19 '15 at 11:49

2 Answers2

1

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

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

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.

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

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.

  • Working fine thanks, just to add if I had a third table that I did not want to involve in the join, where would it fit in? – CBreeze Nov 19 '15 at 12:54
  • @CBreeze - it would depend on what you want to do with it - I suggest opening a new question, with the relevant details of which fields are on which tables included. –  Nov 19 '15 at 13:00
0

You need to change your "where" by removing "or" or add more conditions

 WHERE (CntRef=PresetIDFoxPro) .... other conditions

Just for example

genespos
  • 3,211
  • 6
  • 38
  • 70
  • Isn't the 'AND PresetIDFoxPro<>0' superfluous - if PresetIDFoxPro were equal to zero then the left side of the expression would be true making the entire expression true. – PaulF Nov 19 '15 at 11:58