2

I am trying to search for a client using name and date of birth. My query is included below. 

Select * 
From Client
Inner Join Address
On Client.num = Address.cl_num
Where 
   Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
    Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
And Client.year = 1973
And Client.month = 06
And Client.day = 05

The results however show clients who weren’t born on that day. Is there a problem with my statement?

The query is run on an iSeries server.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Courtney
  • 133
  • 4

1 Answers1

7

The OR clause is short circuiting your selection. OR binds lower than AND. Use parenthesis to group that criteria as follows:

Select * 
From Client
Inner Join Address
On Client.num = Address.cl_num
Where 
(
Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
)
And Client.year = 1973
And Client.month = 06
And Client.day = 05

Precedence rules for operators exist in all languages. For example, 3 + 5 * 2 will give you 13 and not 16 in most programming languages. * has higher precedence that +, the same way AND has higher precedence than OR. If you want the addition, 3+5, to happen before the multiplication, you'll use parentheses: (3 + 5) * 2

jamesallman
  • 211
  • 1
  • 3