Re: Incorrect Query

Hi,

> Why not just use?
>
> WHERE person_id = ''
> OR (firstname = initcap('sharon')
>     AND
>     lastname = initcap('cowling')
>    )
>

Because the user may enter either just the firstname or just the lastname, so the above won't work if I just enter
'cowling'and there are multiple entries of 'cowling' in the database, it returns 0 rows. 


select person_id, initcap(firstname), initcap(lastname), dob, street, city
from person5
where person_id = ''
OR (firstname = initcap('')
AND
lastname = initcap('cowling')
);

 person_id | initcap | initcap | dob | street | city
-----------+---------+---------+-----+--------+------
(0 rows)

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

 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

Regards,

Sharon Cowling


> -----Original Message-----
> From: Joshua b. Jore [mailto:josh@greentechnologist.org]
> Sent: Thursday, 9 May 2002 01:39
> To: Sharon Cowling
> Cc: Pgsql-Novice (E-mail)
> Subject: Re: [NOVICE] Incorrect Query
>
>
> Sharon,
> You were abusing parentheses and confusing the issue. Don't
> do that, it
> just makes the query less readable.
>
> Here is what you actually wrote:
>
> where
> person_id = ''
> or
> (
>    (
>        firstname = initcap('sharon')
>    )
>    or
>    (
>        lastname = initcap('cowling')
>    )
> )
> or
> (
>    (
>        firstname = initcap('sharon')
>        and
>        lastname = initcap('cowling')
>    )
> )
>
> Which is simplified to (removing parentheses where redundant but
> retaining all the logic). Check out that first bit where you match on
> fname or lname. That last bit doesn't even do anything since the other
> name expressions covered it already.
>
> where
> person_id = ''
> or
> firstname = initcap('sharon')
> or
> lastname = initcap('cowling')
> or
> (
>    firstname = initcap('sharon')
>    and
>    lastname = initcap('cowling')
> )
>
> Why not just use?
>
> WHERE person_id = ''
> OR (firstname = initcap('sharon')
>     AND
>     lastname = initcap('cowling')
>    )
>