Incorrect Query

Поиск
Список
Период
Сортировка
От Sharon Cowling
Тема Incorrect Query
Дата
Msg-id 200205080507.g48575N25018@lambton.sslnz.com
обсуждение исходный текст
Ответы Re: Incorrect Query
Re: Incorrect Query
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: more voodoo planner bs :)
Следующее
От: Andrew McMillan
Дата:
Сообщение: Re: Incorrect Query