1

I recently asked a question on StackOverflow (MySQL Returns All Rows When field = 0) regarding a query statement not working in MySQL. I now have a very similar problem, this time using OleDB where I am trying to use a join to include fields that have 0 as an entry, but not select every field in the table as a result.

The new look MySQL query posted in the above question as the accepted answer works without a hitch. However the OleDB counterpart I have written to do almost the same does not. It's a bit messy as the tables are not named very well (I didn't create this database) and I'm getting a simple syntax error;

myQuery.CommandText = "SELECT s.scm_num, s.scm_name, c.cr3_text, q.qsp_value, s.scm_bprefix, s.scm_nxbnum FROM qspreset q INNER JOIN sdccomp s LEFT OUTER JOIN cntref3 c ON s.scm_cntref3=c.cr3_id AND q.qsp_relat=s.scm_invtype AND q.qsp_what=13";

I'm querying another table here as well as the two involved in the LEFT OUTER JOIN and I believe that is where I am making the mistake.

Community
  • 1
  • 1
CBreeze
  • 2,925
  • 4
  • 38
  • 93

1 Answers1

1

Join conditions need to be with the join 

myQuery.CommandText = 
"SELECT s.scm_num, s.scm_name, c.cr3_text, q.qsp_value, s.scm_bprefix, s.scm_nxbnum    
FROM qspreset q   
INNER JOIN sdccomp s 
      on q.qsp_relat = s.scm_invtype AND q.qsp_what = 13  
LEFT OUTER JOIN cntref3 c 
      ON s.scm_cntref3 = c.cr3_id";

q.qsp_what = 13 can be moved to a where

I happen to like this style
In the case of MSSQL T-SQL and some queries with a lot of joins I have gotten more efficient query plan by moving a where condition up into a join. The filter happened early rather that last.

If you don't believe you can put a hard value in a join see SQLfiddle

paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • 1
    And the `q.qsp_what` should simply be in a where. Good catch. – Luaan Nov 19 '15 at 16:34
  • @Luaan Are you sure it will not work in the join also. I do it like that with TSQL. – paparazzo Nov 19 '15 at 16:35
  • It will work in T-SQL, but it definitely *belongs* in a where - it's not a join condition, it's a simple filter. T-SQL is rather forgiving - other languages might not be so happy with something like that. – Luaan Nov 19 '15 at 16:38
  • Conditions on tables that you're joining *to* belong in the `on` clause for that join - `qspreset` is the table you're joining *from*, so conditions on that table only belong in the `where` clause. –  Nov 19 '15 at 17:33
  • @MarkBannister Only? I added a SQLfiddle MySQL 5.6 that indicates you can put a hard value in a join. – paparazzo Nov 19 '15 at 18:34
  • @Frisbee: *belong* is not the same as *can get away with* - you *can get away with* stealing your grandmother's false teeth, but this does not mean they *belong* to you. –  Nov 19 '15 at 23:25
  • Then you need to stop stealing grandma's teeth http://stackoverflow.com/questions/18011883/how-to-increase-perfromance-of-sql-query-for-selecting-from-2-tables/18012170#18012170 – paparazzo Nov 22 '15 at 17:39
  • @Frisbee: The first two `on` conditions in the linked answer are comparing values between the two tables, while the third condition is on a value from the table that is being linked *to* - as I said in my previous comment, **"Conditions on tables that you're joining to belong in the on clause for that join"**. Grandma's teeth remain in her possession. –  Nov 24 '15 at 17:17
  • @MarkBannister Linked to? A join symmetric. Switch the from and join tables is the same join. So if I switched q and s that would make a difference to you? Again do you any evidence that in the join will produce incorrect results? – paparazzo Nov 24 '15 at 17:25
  • "You can lead a man to knowledge, but you cannot make him think." Try rephrasing your last sentence in English. –  Nov 24 '15 at 17:40
  • Oh so now it is English. Really you don't know what that sentence means? What about that switch q and s that you conveniently did not address? – paparazzo Nov 24 '15 at 17:52