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(at)greentechnologist(dot)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')
> )
>