reference to parent qry

reference to parent qry

am 22.04.2008 15:50:47 von jan.schaak

hi,

this is my sql code.
-----start code-----
SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
NBTitel,

(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic

FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
-----end code-----

somewhere in the middle I need to reference to Reeksen.ReeksNR, the
first field in the statement. like now I always have the same value
for each row. and it shout be different for every row. if I change it
into Reeksen.ReeksNR it always returns the nubre of all records in the
table. Someone has I solution

Thanks Jan

Re: reference to parent qry

am 22.04.2008 18:17:38 von JR

Sorry in the middel where the 11 stands

schreef in bericht
news:9acbf173-3923-431c-8b81-0bfb8b3ce687@2g2000hsn.googlegr oups.com...
> hi,
>
> this is my sql code.
> -----start code-----
> SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
> NBTitel,
>
> (SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
> Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
> Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic
>
> FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
> GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
> -----end code-----
>
> somewhere in the middle I need to reference to Reeksen.ReeksNR, the
> first field in the statement. like now I always have the same value
> for each row. and it shout be different for every row. if I change it
> into Reeksen.ReeksNR it always returns the nubre of all records in the
> table. Someone has I solution
>
> Thanks Jan

Re: reference to parent qry

am 23.04.2008 04:58:19 von Tom van Stiphout

On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak@gmail.com wrote:

If I understand you correctly you need to reference the parent query
from the top query, and they both use the same tablename "Reeksen".

Simply use an Alias in the subquery:
.... from Reeksen as ReeksenSub
so you can write things like:
.... where Reeksen.SomeField = ReeksenSub.SomeField

-Tom.



>hi,
>
>this is my sql code.
>-----start code-----
>SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
>NBTitel,
>
>(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
>Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic
>
>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
>GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
>-----end code-----
>
>somewhere in the middle I need to reference to Reeksen.ReeksNR, the
>first field in the statement. like now I always have the same value
>for each row. and it shout be different for every row. if I change it
>into Reeksen.ReeksNR it always returns the nubre of all records in the
>table. Someone has I solution
>
>Thanks Jan

Re: reference to parent qry

am 23.04.2008 07:25:47 von Tom van Stiphout

On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
wrote:

Oops. I meant "... from the sub query ..."
-Tom.


>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak@gmail.com wrote:
>
>If I understand you correctly you need to reference the parent query
>from the top query, and they both use the same tablename "Reeksen".
>
>Simply use an Alias in the subquery:
>... from Reeksen as ReeksenSub
>so you can write things like:
>... where Reeksen.SomeField = ReeksenSub.SomeField
>
>-Tom.
>
>
>
>>hi,
>>
>>this is my sql code.
>>-----start code-----
>>SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
>>NBTitel,
>>
>>(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
>>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
>>Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic
>>
>>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
>>GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
>>-----end code-----
>>
>>somewhere in the middle I need to reference to Reeksen.ReeksNR, the
>>first field in the statement. like now I always have the same value
>>for each row. and it shout be different for every row. if I change it
>>into Reeksen.ReeksNR it always returns the nubre of all records in the
>>table. Someone has I solution
>>
>>Thanks Jan

Re: reference to parent qry

am 24.04.2008 11:59:22 von JR

I found some of that info on the net. wat i was missing in all that info was
a way how I must do it. I diden'tonderstand there examples . So how do I
give that query another name



Tom van Stiphout" schreef in bericht
..news:02it041cebhka0oepkqr61udrhefiq1t1i@4ax.com...
> On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
> wrote:
>
> Oops. I meant "... from the sub query ..."
> -Tom.
>
>
>>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak@gmail.com wrote:
>>
>>If I understand you correctly you need to reference the parent query
>>from the top query, and they both use the same tablename "Reeksen".
>>
>>Simply use an Alias in the subquery:
>>... from Reeksen as ReeksenSub
>>so you can write things like:
>>... where Reeksen.SomeField = ReeksenSub.SomeField
>>
>>-Tom.
>>
>>
>>
>>>hi,
>>>
>>>this is my sql code.
>>>-----start code-----
>>>SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
>>>NBTitel,
>>>
>>>(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
>>>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
>>>Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic
>>>
>>>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
>>>GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
>>>-----end code-----
>>>
>>>somewhere in the middle I need to reference to Reeksen.ReeksNR, the
>>>first field in the statement. like now I always have the same value
>>>for each row. and it shout be different for every row. if I change it
>>>into Reeksen.ReeksNR it always returns the nubre of all records in the
>>>table. Someone has I solution
>>>
>>>Thanks Jan

Re: reference to parent qry

am 24.04.2008 12:38:55 von Roger

On Apr 24, 3:59=A0am, "JR" wrote:
> I found some of that info on the net. wat i was missing in all that info w=
as
> a way how I must do it. I diden'tonderstand there examples . So how do I
> give that query =A0another name
>
> Tom van Stiphout" schreef in bericht
> .news:02it041cebhka0oepkqr61udrhefiq1t1i@4ax.com...
>
>
>
> > On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
> > wrote:
>
> > Oops. I meant "... from the sub query ..."
> > -Tom.
>
> >>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.sch...@gmail.com wrote:
>
> >>If I understand you correctly you need to reference the parent query
> >>from the top query, and they both use the same tablename "Reeksen".
>
> >>Simply use an Alias in the subquery:
> >>... from Reeksen as ReeksenSub
> >>so you can write things like:
> >>... where Reeksen.SomeField =3D ReeksenSub.SomeField
>
> >>-Tom.
>
> >>>hi,
>
> >>>this is my sql code.
> >>>-----start code-----
> >>>SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
> >>>NBTitel,
>
> >>>(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
> >>>Strips ON Reeksen.ReeksNR =3D Strips.ReeksNR) INNER JOIN Pictures ON
> >>>Strips.Uniek =3D Pictures.Uniek where reeksen.ReeksNR=3D11) AS AantalPi=
c
>
> >>>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR =3D Strips.ReeksNR
> >>>GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
> >>>-----end code-----
>
> >>>somewhere in the middle I need to reference to Reeksen.ReeksNR, the
> >>>first field in the statement. like now I always have the same value
> >>>for each row. and it shout be different for every row. if I change it
> >>>into Reeksen.ReeksNR it always returns the nubre of all records in the
> >>>table. Someone has I solution
>
> >>>Thanks Jan- Hide quoted text -
>
> - Show quoted text -

because you reference the table Reeksen twice, you need to use an
alias to differentiate between the two, see references to ReeksenSub
below

SELECT ReeksenSub.ReeksNR, ReeksenSub.ReeksText, Count(Strips.Titel)
AS
NBTitel,


(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER
JOIN
Strips ON Reeksen.ReeksNR =3D Strips.ReeksNR) INNER JOIN Pictures ON
Strips.Uniek =3D Pictures.Uniek where reeksen.ReeksNR=3D11) AS AantalPic


FROM Reeksen as ReeksenSub INNER JOIN Strips ON ReeksenSub.ReeksNR =3D
Strips.ReeksNR
GROUP BY ReeksenSub.ReeksNR, ReeksenSub.ReeksText;

Re: reference to parent qry

am 24.04.2008 17:20:11 von JR

"Roger" schreef in bericht
news:27a4a1f3-f406-4581-b178-26410e1db3ad@m3g2000hsc.googleg roups.com...
On Apr 24, 3:59 am, "JR" wrote:
> I found some of that info on the net. wat i was missing in all that info
> was
> a way how I must do it. I diden'tonderstand there examples . So how do I
> give that query another name
>
> Tom van Stiphout" schreef in bericht
> .news:02it041cebhka0oepkqr61udrhefiq1t1i@4ax.com...
>
>
>
> > On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
> > wrote:
>
> > Oops. I meant "... from the sub query ..."
> > -Tom.
>
> >>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.sch...@gmail.com wrote:
>
> >>If I understand you correctly you need to reference the parent query
> >>from the top query, and they both use the same tablename "Reeksen".
>
> >>Simply use an Alias in the subquery:
> >>... from Reeksen as ReeksenSub
> >>so you can write things like:
> >>... where Reeksen.SomeField = ReeksenSub.SomeField
>
> >>-Tom.
>
> >>>hi,
>
> >>>this is my sql code.
> >>>-----start code-----
> >>>SELECT Reeksen.ReeksNR, Reeksen.ReeksText, Count(Strips.Titel) AS
> >>>NBTitel,
>
> >>>(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER JOIN
> >>>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
> >>>Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic
>
> >>>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
> >>>GROUP BY Reeksen.ReeksNR, Reeksen.ReeksText;
> >>>-----end code-----
>
> >>>somewhere in the middle I need to reference to Reeksen.ReeksNR, the
> >>>first field in the statement. like now I always have the same value
> >>>for each row. and it shout be different for every row. if I change it
> >>>into Reeksen.ReeksNR it always returns the nubre of all records in the
> >>>table. Someone has I solution
>
> >>>Thanks Jan- Hide quoted text -
>
> - Show quoted text -

because you reference the table Reeksen twice, you need to use an
alias to differentiate between the two, see references to ReeksenSub
below

SELECT ReeksenSub.ReeksNR, ReeksenSub.ReeksText, Count(Strips.Titel)
AS
NBTitel,


(SELECT Count(strips.Titel) AS AantalVanTitel FROM (Reeksen INNER
JOIN
Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
Strips.Uniek = Pictures.Uniek where reeksen.ReeksNR=11) AS AantalPic


FROM Reeksen as ReeksenSub INNER JOIN Strips ON ReeksenSub.ReeksNR =
Strips.ReeksNR
GROUP BY ReeksenSub.ReeksNR, ReeksenSub.ReeksText;


thanks. now I get it