At 10:08 AM 6/04/2004 +1200, you wrote:

>Hi,
>
>I am trying to do a correlated sub query, in the select.
>
>This works fine, except I want to make the sub query reference the main
>query. I tried to use an alias in the main query and reference that
>alias in the sub query nu firebird gave me an error..
>
>I have included the query below. You will see that the where clause of
>the sub query is trying to reference an alias from the "outer query"
>(ScheduleID)
>(when the scheduleID alias is replaced with a number then all works
>fine)
>is this sort of thing possible in firbird and if so how do I use it.
>
>SELECT
> EE_SP_SCHEDULE.ID ScheduleID,
> EE_SP_SCHEDULE.CAMPAIGN_NAME,
> EE_SECURITY.USER_NAME,
> EE_SECURITY.EMAIL_ADDRESS,
> (
> SELECT COUNT( * )
>FROM EE_SP_SCHEDULE
> INNER JOIN EE_SP_SCHEDULE_TIME ON (EE_SP_SCHEDULE.ID =
>EE_SP_SCHEDULE_TIME.EE_SP_SCHEDULE_ID)
> INNER JOIN EE_MESSAGE ON (EE_SP_SCHEDULE_TIME.ID =
>EE_MESSAGE.EE_SP_SCHEDULE_TIME_ID)
>where EE_SP_SCHEDULE.ID = ScheduleID
> )
> FROM EE_SP_SCHEDULE
> INNER JOIN EE_SECURITY ON (EE_SP_SCHEDULE.EE_SECURITY_ID =
>EE_SECURITY.ID)
>
>thank you in advance for your help.

The correlation has to correlate the relationships between the tables, so
you need table aliases.

SELECT
s1. EE_SP_SCHEDULE.ID,
s1.EE_SP_SCHEDULE.CAMPAIGN_NAME,
s1.EE_SECURITY.USER_NAME,
s1.EE_SECURITY.EMAIL_ADDRESS,
(
SELECT COUNT( * )
FROM EE_SP_SCHEDULE s2
INNER JOIN EE_SP_SCHEDULE_TIME st
ON
s2.EE_SP_SCHEDULE.ID = st.EE_SP_SCHEDULE_TIME.EE_SP_SCHEDULE_ID
INNER JOIN EE_MESSAGE m
ON s2.EE_SP_SCHEDULE_TIME.ID = m.EE_MESSAGE.EE_SP_SCHEDULE_TIME_ID
where s2.EE_SP_SCHEDULE.ID = s1.EE_SP_SCHEDULE.ID
)
FROM EE_SP_SCHEDULE s1
INNER JOIN EE_SECURITY sec
ON s1.EE_SP_SCHEDULE.EE_SECURITY_ID = sec.EE_SECURITY.ID

/heLen