Incorrect Query

From: Sharon Cowling <sharon(dot)cowling(at)sslnz(dot)com>
To: "Pgsql-Novice (E-mail)" <pgsql-novice(at)postgresql(dot)org>
Subject: Incorrect Query
Date: 2002-05-08 05:06:00
Message-ID: 200205080507.g48575N25018@lambton.sslnz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I'm working on an application, the user enters the customers id or their firstname or their lastname or their firstname and lastname. The problem is when they enter their firstname AND their lastname it returns as if you had entered either their firstname OR their lastname. I must be missing brackets somewhere, but I can't figure it out, I've tried everything I 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Reinhard Hnat 2002-05-08 05:11:44 View don't know table
Previous Message Tom Lane 2002-05-08 04:44:18 Re: more voodoo planner bs :)