Problem with inner queries
I'm doing the following query
SELECT codazorig, (
SELECT cinnodo FROM nodi
WHERE nodi.codaznodo = codazorig)
AS cinnodoorig, codazdest, (
SELECT cinnodo FROM nodi
WHERE nodi.codaznodo = codazdest)
AS cinnododest
FROM traduzionearchi
that is used to convert a field to another one (codazorig to
cinnodoorig and codazdest to cinnododest).
Then I want to use the obtained fiels to filter the data in another
table. I tried the following code but got an error message telling me
that the columns cinnododest and cinnodoorig could not be found.
Here's the failing code:
SELECT codazorig, na, nb, archi.cinarco, (
SELECT cinnodo FROM nodi
WHERE nodi.codaznodo = codazorig)
AS cinnodoorig, codazdest, (
SELECT cinnodo FROM nodi
WHERE nodi.codaznodo = codazdest)
AS cinnododest
FROM traduzionearchi,archi WHERE (archi.cinnodoor=cinnodoorig ANDarchi.cinnododes=cinnododest);
How can I fix this?
Thanks a lot
Andrea