Thread: better way

Is there a better way to write this:

SELECT a.oid_fld FROM for_payment a
WHERE a.serial_fld <> 2
AND EXISTS
(SELECT b.char_fld FROM for_payment b
WHERE b.serial_fld = 2
AND b.char_fld = a.char_fld)

Table for_payment
+-------------+------------+-----------+| serial_fld   |  char_fld  |  oid_fld   | 
+-------------+------------+-----------+| 1               |  test1       |  456701  | | 2               |  test1
| 456702  | | 3               |  test1       |  456703  | | 4               |  test2       |  456704  | | 5
 |  test3       |  456705  | 
 
+-------------+------------+-----------+

sample problem:
Assuming that I pick 2 in serial_fld.
How do I get the oid_fld of the rows with char_fld equal
to the char_fld of 2?
I should get these:

+-----------+|  oid_fld   | 
+-----------+|  456701  | |  456702  | 
+-----------+

Thanks.

Didn't test this, but I think it should also work:

SELECT a.oid_fld
FROM for_payment a, for_payment b
WHERE b.serial_fld = 2
AND b.char_fld = a.char_fld
AND a.serial_fld <> 2

--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br

> Is there a better way to write this:
>
> SELECT a.oid_fld FROM for_payment a
> WHERE a.serial_fld <> 2
> AND EXISTS
> (SELECT b.char_fld FROM for_payment b
> WHERE b.serial_fld = 2
> AND b.char_fld = a.char_fld)
>
> Table for_payment
> +-------------+------------+-----------+
>  | serial_fld   |  char_fld  |  oid_fld   |
> +-------------+------------+-----------+
>  | 1               |  test1       |  456701  |
>  | 2               |  test1       |  456702  |
>  | 3               |  test1       |  456703  |
>  | 4               |  test2       |  456704  |
>  | 5               |  test3       |  456705  |
> +-------------+------------+-----------+
>
> sample problem:
> Assuming that I pick 2 in serial_fld.
> How do I get the oid_fld of the rows with char_fld equal
> to the char_fld of 2?
> I should get these:
>
> +-----------+
>  |  oid_fld   |
> +-----------+
>  |  456701  |
>  |  456702  |
> +-----------+
>
> Thanks.