reference to parent qry

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jan.schaak@gmail.com
    #1

    reference to parent qry

    hi,

    this is my sql code.
    -----start code-----
    SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
    NBTitel,

    (SELECT Count(strips.Ti tel) 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.ReeksTe xt;
    -----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
    Tags: None
    • JR
      #2
      Re: reference to parent qry

      Sorry in the middel where the 11 stands

      <jan.schaak@gma il.comschreef in bericht
      news:9acbf173-3923-431c-8b81-0bfb8b3ce687@2g 2000hsn.googleg roups.com...
      hi,
      >
      this is my sql code.
      -----start code-----
      SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
      NBTitel,
      >
      (SELECT Count(strips.Ti tel) 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.ReeksTe xt;
      -----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

        Comment

        • Tom van Stiphout
          #3
          Re: reference to parent qry

          On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak@gmai l.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.SomeFie ld = ReeksenSub.Some Field

          -Tom.


          >hi,
          >
          >this is my sql code.
          >-----start code-----
          >SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
          >NBTitel,
          >
          >(SELECT Count(strips.Ti tel) 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.ReeksTe xt;
          >-----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

            Comment

            • Tom van Stiphout
              #4
              Re: reference to parent qry

              On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
              <no.spam.tom774 4@cox.netwrote:

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

              >On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak@gmai l.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.SomeFie ld = ReeksenSub.Some Field
              >
              >-Tom.
              >
              >
              >
              >>hi,
              >>
              >>this is my sql code.
              >>-----start code-----
              >>SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
              >>NBTitel,
              >>
              >>(SELECT Count(strips.Ti tel) AS AantalVanTitel FROM (Reeksen INNER JOIN
              >>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
              >>Strips.Unie k = Pictures.Uniek where reeksen.ReeksNR =11) AS AantalPic
              >>
              >>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
              >>GROUP BY Reeksen.ReeksNR , Reeksen.ReeksTe xt;
              >>-----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

                Comment

                • JR
                  #5
                  Re: reference to parent qry

                  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'tondersta nd there examples . So how do I
                  give that query another name



                  Tom van Stiphout" <no.spam.tom774 4@cox.netschree f in bericht
                  ..news:02it041c ebhka0oepkqr61u drhefiq1t1i@4ax .com...
                  On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
                  <no.spam.tom774 4@cox.netwrote:
                  >
                  Oops. I meant "... from the sub query ..."
                  -Tom.
                  >
                  >
                  >>On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.schaak@gmai l.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.SomeFie ld = ReeksenSub.Some Field
                  >>
                  >>-Tom.
                  >>
                  >>
                  >>
                  >>>hi,
                  >>>
                  >>>this is my sql code.
                  >>>-----start code-----
                  >>>SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
                  >>>NBTitel,
                  >>>
                  >>>(SELECT Count(strips.Ti tel) AS AantalVanTitel FROM (Reeksen INNER JOIN
                  >>>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
                  >>>Strips.Uni ek = Pictures.Uniek where reeksen.ReeksNR =11) AS AantalPic
                  >>>
                  >>>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
                  >>>GROUP BY Reeksen.ReeksNR , Reeksen.ReeksTe xt;
                  >>>-----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

                    Comment

                    • Roger
                      #6
                      Re: reference to parent qry

                      On Apr 24, 3:59 am, "JR" <xx....@xx.xxwr ote:
                      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'tondersta nd there examples . So how do I
                      give that query  another name
                      >
                      Tom van Stiphout" <no.spam.tom7.. .@cox.netschree f in bericht
                      .news:02it041ce bhka0oepkqr61ud rhefiq1t1i@4ax. com...
                      >
                      >
                      >
                      On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
                      <no.spam.tom7.. .@cox.netwrote:
                      >
                      Oops. I meant "... from the sub query ..."
                      -Tom.
                      >
                      >On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.sch...@gmai l.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.SomeFie ld = ReeksenSub.Some Field
                      >
                      >-Tom.
                      >
                      >>hi,
                      >
                      >>this is my sql code.
                      >>-----start code-----
                      >>SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
                      >>NBTitel,
                      >
                      >>(SELECT Count(strips.Ti tel) AS AantalVanTitel FROM (Reeksen INNER JOIN
                      >>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
                      >>Strips.Unie k = Pictures.Uniek where reeksen.ReeksNR =11) AS AantalPic
                      >
                      >>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
                      >>GROUP BY Reeksen.ReeksNR , Reeksen.ReeksTe xt;
                      >>-----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.Reek sNR, ReeksenSub.Reek sText, Count(Strips.Ti tel)
                      AS
                      NBTitel,


                      (SELECT Count(strips.Ti tel) 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.Reek sNR =
                      Strips.ReeksNR
                      GROUP BY ReeksenSub.Reek sNR, ReeksenSub.Reek sText;

                        Comment

                        • JR
                          #7
                          Re: reference to parent qry


                          "Roger" <lesperancer@na tpro.comschreef in bericht
                          news:27a4a1f3-f406-4581-b178-26410e1db3ad@m3 g2000hsc.google groups.com...
                          On Apr 24, 3:59 am, "JR" <xx....@xx.xxwr ote:
                          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'tondersta nd there examples . So how do I
                          give that query another name
                          >
                          Tom van Stiphout" <no.spam.tom7.. .@cox.netschree f in bericht
                          .news:02it041ce bhka0oepkqr61ud rhefiq1t1i@4ax. com...
                          >
                          >
                          >
                          On Tue, 22 Apr 2008 19:58:19 -0700, Tom van Stiphout
                          <no.spam.tom7.. .@cox.netwrote:
                          >
                          Oops. I meant "... from the sub query ..."
                          -Tom.
                          >
                          >On Tue, 22 Apr 2008 06:50:47 -0700 (PDT), jan.sch...@gmai l.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.SomeFie ld = ReeksenSub.Some Field
                          >
                          >-Tom.
                          >
                          >>hi,
                          >
                          >>this is my sql code.
                          >>-----start code-----
                          >>SELECT Reeksen.ReeksNR , Reeksen.ReeksTe xt, Count(Strips.Ti tel) AS
                          >>NBTitel,
                          >
                          >>(SELECT Count(strips.Ti tel) AS AantalVanTitel FROM (Reeksen INNER JOIN
                          >>Strips ON Reeksen.ReeksNR = Strips.ReeksNR) INNER JOIN Pictures ON
                          >>Strips.Unie k = Pictures.Uniek where reeksen.ReeksNR =11) AS AantalPic
                          >
                          >>FROM Reeksen INNER JOIN Strips ON Reeksen.ReeksNR = Strips.ReeksNR
                          >>GROUP BY Reeksen.ReeksNR , Reeksen.ReeksTe xt;
                          >>-----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.Reek sNR, ReeksenSub.Reek sText, Count(Strips.Ti tel)
                          AS
                          NBTitel,


                          (SELECT Count(strips.Ti tel) 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.Reek sNR =
                          Strips.ReeksNR
                          GROUP BY ReeksenSub.Reek sNR, ReeksenSub.Reek sText;


                          thanks. now I get it

                            Comment

                            Previous template Next
                            Working...