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

Sorry in the middel where the 11 stands

<jan.schaak [at] gmail.com> schreef in bericht
news:9acbf173-3923-431c-8b81-0bfb8b3ce687 [at] 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

On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak [at] 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

On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
<no.spam.tom7744 [at] cox.net> wrote:

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


>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak [at] 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

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" <no.spam.tom7744 [at] cox.net> schreef in bericht
..news:02it041cebhka0oepkqr61udrhefiq1t1i [at] 4ax.com...
> On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
> <no.spam.tom7744 [at] cox.net> wrote:
>
> Oops. I meant "... from the sub query ..."
> -Tom.
>
>
>>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak [at] 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

On Apr 24, 3:59=A0am, "JR" <xx.... [at] xx.xx> 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" <no.spam.tom7... [at] cox.net> schreef in bericht
> .news:02it041cebhka0oepkqr61udrhefiq1t1i [at] 4ax.com...
>
>
>
> > On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
> > <no.spam.tom7... [at] cox.net> wrote:
>
> > Oops. I meant "... from the sub query ..."
> > -Tom.
>
> >>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.sch... [at] 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;

"Roger" <lesperancer [at] natpro.com> schreef in bericht
news:27a4a1f3-f406-4581-b178-26410e1db3ad [at] m3g2000hsc.googleg roups.com...
On Apr 24, 3:59 am, "JR" <xx.... [at] xx.xx> 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" <no.spam.tom7... [at] cox.net> schreef in bericht
> .news:02it041cebhka0oepkqr61udrhefiq1t1i [at] 4ax.com...
>
>
>
> > On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
> > <no.spam.tom7... [at] cox.net> wrote:
>
> > Oops. I meant "... from the sub query ..."
> > -Tom.
>
> >>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.sch... [at] 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