Incorrect Query - Mailing list pgsql-novice

From Sharon Cowling
Subject Incorrect Query
Date
Msg-id 200205080507.g48575N25018@lambton.sslnz.com
Whole thread Raw
Responses Re: Incorrect Query
Re: Incorrect Query
List pgsql-novice
Hi,

I'm working on an application, the user enters the customers id or their firstname or their lastname or their firstname
andlastname.  The problem is when they enter their firstname AND their lastname it returns as if you had entered either
theirfirstname OR their lastname.  I must be missing brackets somewhere, but I can't figure it out, I've tried
everythingI could think of, does anyone have any ideas?  

Query:
select person_id, initcap(firstname), initcap(lastname), dob, street, city
from person5
where person_id = ''
or ( ( firstname = initcap('sharon') )
or ( lastname = initcap('cowling') ) )
or ( ( firstname = initcap('sharon')
and lastname = initcap('cowling')  ) )
order by lastname;

Results:
 person_id | initcap | initcap  |    dob     | street  |    city
-----------+---------+----------+------------+---------+------------
      1018 | Katrina | Cowling  | 07/07/1976 | Long St | Wellington
       858 | Sharon  | Cowling  | 16/10/1979 | A Grove | Wellington
      1060 | Sharon  | O'neill  | 11/11/1977 | Fsddf   | Fdsfds
      1062 | Sharon  | O'ray    | 11/11/1977 | Dfs     | Sfsf
      1061 | Sharon  | O'reilly | 11/11/1977 | Sfds    | Sfdsfs
       880 | Sharon  | Smiley   | 16/11/1979 | The St  | The City
(6 rows)



sharon=> \d person5
                   Table "person5"
    Attribute     |         Type          | Modifier
------------------+-----------------------+----------
 person_id        | integer               | not null
 firstname        | character varying(25) | not null
 lastname         | character varying(25) | not null
 dob              | date                  | not null
 street           | character varying(50) | not null
 suburb           | character varying(50) |
 city             | character varying(50) | not null
 homephone        | character varying(15) |
 workphone        | character varying(15) |
 mobile           | character varying(15) |
 type             | character varying(30) | not null
 date_approved    | date                  | not null
 approved_by      | character varying(50) | not null
 vehicle_type     | character varying(50) |
 vehicle_rego     | character varying(6)  |
 drivers_licence  | character varying(10) |
 firearms_licence | character varying(20) |
 notes            | character varying(80) |
 status           | character varying(10) |
Indices: firstname_idx,
         fullname_idx,
         lastname_idx,
         person5_drivers_licence_key,
         person5_firearms_licence_key,
         person5_pkey


Regards,

Sharon Cowling



pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: more voodoo planner bs :)
Next
From: Andrew McMillan
Date:
Subject: Re: Incorrect Query