Stef Mientki <[EMAIL PROTECTED]> wrote:
I thought this would work,
SELECT *
FROM Patient_Text
INNER JOIN
(
SELECT *
FROM Patient
INNER JOIN Opnamen
ON Patient.PatNr = Opnamen.PatNr
)
ON Patient.PatNr = Patient_Text.PatNr
^^^^^^^^^^^^
But I get an error on the second use of Patient.PatNr.
The whole subselect is treated as a single table, you can't address
individual tables that went into it anymore. You can give the subselect
an alias:
SELECT *
FROM Patient_Text
INNER JOIN
(
SELECT *
FROM Patient
INNER JOIN Opnamen
ON Patient.PatNr = Opnamen.PatNr
) AS Patient
ON Patient.PatNr = Patient_Text.PatNr
However, in this particular case you'll have a problem, since the result
of subselect contains two columns named PatNr (one from Patient and one
from Opnamen). So Patient.PatNr will be ambiguous. You could explicitly
name columns in the subselect and give them aliases, then refer to them
as SubselectAlias.ColumnAlias. But this way you won't be able to use *
notation.
And of course, this particular query can be rewritten as
SELECT *
FROM Patient_Text JOIN Patient ON Patient.PatNr = Patient_Text.PatNr
JOIN Opnamen ON Patient.PatNr = Opnamen.PatNr;
It will also likely be much more efficient: all these nested subselects
pretty much disable SQLite optimizer.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
