Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group