syntax error in my SQL Select Statement?

Question

Getting error stating between operator without an and but I have an and so I don't understand. Thank you in advance

Syntax Error

A picture of the question

select 
    a.student_name, a.test_score, b.letter_grade
from 
     sec1311_student_scores a 
inner join
     sec1311_grade_ranges b on a.testscore between b.beginning_score and b.endingscore
order by 
    a.student_name;

That's some serious code-fu to achieve that message. Are beginning_Score or ending_score null? odd that one has a _ and one doesn't... are the "Scores" both numeric datatypes? could you need to coalesce(b.beginning_score,0) and coalesce(b.endingscore,0)? or should the last score always be 100 or something? — xQbert
– xQbert, Commented Nov 7, 2016 at 21:05
What RDBMS are you using? Some (like maybe Access) may want the z BETWEEN x AND y clause to be enclosed in parentheses (z BETWEEN x AND y) — Michael Berkowski
– Michael Berkowski, Commented Nov 7, 2016 at 21:08
should the ON statement link student_scores and grade_ranges? — McNets
– McNets, Commented Nov 7, 2016 at 21:10

D Stanley · Accepted Answer · 2016-11-07 22:22:53Z

2

Access does not support BETWEEN in join clauses. From the documentation:

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
The INNER JOIN operation has these parts:

Part           | Description 
----           | -------------
table1, table2 | The names of the tables from which records are combined.
field1, field2 | The names of the fields that are joined. If they are not numeric, the fields must be of the same data type and contain the same kind of data, but they do not have to have the same name.
compopr        | Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

You can, however, specify multiple join conditions, so you should be able to do:

select 
    a.student_name, a.test_score, b.letter_grade
from 
     sec1311_student_scores a 
inner join
     sec1311_grade_ranges b on a.testscore >= b.beginning_score 
                        and on a.testscore <= b.endingscore
order by 
    a.student_name;

answered Nov 7, 2016 at 22:22

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas G Over a year ago

You are right Access doesnt support BETWEEN in the ON clause. And just for the sake of being accurate : this is usually called a non equi-join

Clay Over a year ago

it doesnt work still with the new code either. but thanks for the info on the between

Gustav · Accepted Answer · 2016-11-07 21:31:09Z

1

Try with this:

select 
    a.student_name, a.test_score, b.letter_grade
from 
    sec1311_student_scores as a, 
    sec1311_grade_ranges as b 
where
    a.testscore between b.beginning_score and b.ending_score
order by 
    a.student_name;

answered Nov 7, 2016 at 21:31

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

2 Comments

Clay Over a year ago

That works but i was asked to write what you just wrote with a inner join instead of the where clause. Thank you for your effort though it is greatly appreciated

Gustav Over a year ago

You can be asked many things, sometimes the impossible but, in Access SQL, this is the method. It is even displayable in the GUI designer which some join methods not are.

Collectives™ on Stack Overflow

syntax error in my SQL Select Statement?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related