--- In firebird-support@yahoogroups.com, Yves Glodt <yg@m...> wrote:
> Hello,
>
> please consider this query:
>
> SELECT
> WT_CORR.EFFECT_DATE,
> WT_CORR.CORR_TYPE,
> WT_CORR.CORR_STATE,
> WT_CORR.CORR_INFO_1,
> WT_CORR.CORR_INFO_2,
> WT_CORR.CORR_INFO_3,
> WT_CORR.TYPE_CORR,
> WT_CORR.CREATOR,
> WT_NOMS.NAME_NAME,
> WT_MOTIFS.REASON_COLOUR,
> WT_MOTIFS.REASON_TYPE
> FROM WT_CORR,WT_NOMS,WT_MOTIFS
> WHERE WT_CORR.IPN=31 AND
> WT_CORR.EFFECT_DATE >= $start AND WT_CORR.EFFECT_DATE <= $end
AND
> WT_NOMS.NAME_TYPE=1400 AND
> (WT_CORR.CORR_TYPE = WT_NOMS NAME_NUMBER) AND
> (WT_CORR.CORR_TYPE = WT_NOMS.REASON_NUMBER)
> ORDER BY WT_CORR.EFFECT_DATE
>
>
> This is a rough draft, but it works well.
> The 9th column it returns is the textual name of the numeric value
from
> column 2. Names are stored in the WT_NOMS table, and in this query
are
> linked through the section number "WT_NOMS.NAME_TYPE = 1400"
>
>
> But I need to get another name from the WT_NOMS table, in a section
that
> I get in the 10th column, which of course will vary for each
returned
> row...
>
> Is there a way to use values returned in a row in a WHERE clause in
the
> same query, like "where WT_NOMS.NAME_TYPE=$(value_from_column #10)"
> I was thinkin about a subquery, but I would also need a former
result in
> the WHERE.
>
> Is this possible at all with bare sql?

Probably, the reason that you haven't got any reply may be that we do
not quite understand what you want to do.

Try something like:

SELECT
WT_CORR.EFFECT_DATE,
WT_CORR.CORR_TYPE,
WT_CORR.CORR_STATE,
WT_CORR.CORR_INFO_1,
WT_CORR.CORR_INFO_2,
WT_CORR.CORR_INFO_3,
WT_CORR.TYPE_CORR,
WT_CORR.CREATOR,
WN1.NAME_NAME,
WT_MOTIFS.REASON_COLOUR,
WN2.NAME_NAME as COLOURNAME
WT_MOTIFS.REASON_TYPE
FROM WT_CORR
JOIN WT_NOMS WN1 ON
WN1.NAME_NUMBER = WT_CORR.CORR_TYPE
JOIN WT_NOMS WN2 ON
WN2.NAME_NUMBER = WT_MOTIFS.REASON_COLOUR
JOIN WT_MOTIFS ON
WT_CORR.CORR_TYPE = WT_MOTIFS.REASON_NUMBER
WHERE
WT_CORR.IPN=31 AND
WT_CORR.EFFECT_DATE >= $start AND WT_CORR.EFFECT_DATE <= $end AND
WN1.NAME_TYPE=1400 AND
WN2.NAME_TYPE=1400 /*I don't know whether you want this
ORDER BY WT_CORR.EFFECT_DATE

As you see, I've also rewritten your query from SQL-89 to SQL-92 (i.e.
used JOIN). This query should work whether or not you do that, but in
general it is preferable to use JOIN.

HTH,
Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation