Hi Rafael!

I find your description a bit confusing, you say that you want Name1 to
have a certain value, but your query just refers to Name2 (i.e. to
different tables).

A guess of what you may want is (provided there are no duplicates for
the combination Table2.Cod1, table2.Cod2):

SELECT T3.Cod1, T3.Cod2, T3.Cod3, T3.Name3
FROM Table3 T3
JOIN Table2 T2 ON T3.Cod1 = T2.Cod1
AND T3.Cod2 = T2.Cod2
WHERE UPPER(T2.Name2) STARTING 'ABC'

This should work if your tables are as I imagine, but the performance
may not be ideal if your tables contains lots of records (millions). If
so, I'd recommend either using an expression index (Firebird 2.1) or add
an indexed field Table2.Name2UC which is populated through a trigger and
use T2.Name2UC rather than T2.Name2. The reason being that UPPER
prevents the use of indexes (well, except expression indexes). I assume
Cod1 and Cod2 already are part of indexes or keys.

If there can be duplicates in Table2, then you cannot use the JOIN
above. If so, try:

SELECT T3.Cod1, T3.Cod2, T3.Cod3, T3.Name3
FROM Table3 T3
WHERE EXISTS(SELECT NULL FROM Table2 T2
WHERE T3.Cod1 = T2.Cod1
AND T3.Cod2 = T2.Cod2
AND UPPER(T2.Name2) STARTING 'ABC')

Generally, I find EXISTS a better option than IN (<subselect>), and
normally only use IN (<constant values>) myself, but I admit that I am a
bit biased.

HTH and welcome to Firebird,
Set

PS! Generally, I think it is more common to have one meaningless,
autogenerated primary key field for every table, than to have a primary
key consisting of several fields. Though the main thing to remember, is
that a primary key field should never be a field with any meaning
(primary key fields are more troublesome to change than normal fields in
a table, and if you used - say - postcode as a primary key field, you
would potentially have to remove foreign keys and recompile triggers
when the postcode was increased by one letter. If the PK field was
meaningless, updating the postcode would be simpler).

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.
>
> Thank you very much,
>
> Best regards,
>
> Rafael