Hi Adam!! Sorry for taking so long, but just now I could effectively try
your suggestion and it worked wonderfully…..



Thank you so much!!



Best regards,



Rafael



De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de Adam
Enviada em: terça-feira, 27 de janeiro de 2009 19:51
Para: firebird-support@yahoogroups.com
Assunto: [firebird-support] Re: More than one column in "IN" clause



--- In firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> , "RCN Listas" <rcn_listas@...>
wrote:

>
> Hi there!!
>
>
>
> First of all I would like to thank all of you for receiving me here.
>
>
>
> I'm facing a big doubt for me, because I'm not expert in database or

little

> complex queries, but simple selects and DMLs, as a developer.
>
>
>
> The situation is the following:
>
>
>
> 1) I have 3 tables, as follows (table and columns):
>
> Table1
>
> Cod1
>
> Name1
>
>
>
> Table2
>
> Cod1 (from Table1)
>
> Cod2
>
> Name2
>
>
>
> Table3
>
> Cod1 (from Table2)
>
> Cod2 (from Table2)
>
> Cod3
>
> Name3
>
>
>
> 2) I'd like to select all records from Table3 where the related

Name1 start

> with "ABC", then I wrote the following:
>
> select * from Table3 where Table3.Cod2 in (select Table2.Cod2 from

Table2

> where upper(Table2.Name2) like 'ABC%')
>
>
>
> 3) As, in Table2, I reset Cod2 for each new increment of Cod1, I have my
> problem: supposing when I run the previous select I receive a value 1 as
> Cod2 from Table2 (nested query) than, in Table3 I will have all records
> where Cod2 is equal to 1, independent of Cod1, which is very important.
>
>
>
> I thought that if I could use de Table3.Cod1 and Table3.Cod2 in the "IN"
> statement, I could solve the problems, so I would be matching both 2
> segments from my primary key, but I don't know how to do this or similar
> solution.
>
>
>
> So, I would very glad if anyone can help me.

It is (usually, there are exceptions) a bad design to have a primary
key consisting of:

* more than one field
* a field with real world meaning (use a surrogate field).

Most of these problems would simply go away if the table design was a
bit better.

Your query can be done using an exists statement instead of IN

select *
from Table3
where exists
(
select
from Table2
where Table2.Cod1 = Table3.Cod1
and Table2.Cod2 = Table3.Cod2
and upper(Table2.Name2) like 'ABC%'
)

or if you preferred multiple rows returned for multiple matches (if
that is possible), an inner join query.

select Table3.*
from Table3
inner join Table2 on (Table2.Cod1 = Table3.Cod1 and Table2.Cod2 =
Table3.Cod2)
where upper(Table2.Name2) like 'ABC%'

Adam





[Non-text portions of this message have been removed]