Loading ...
Sorry, an error occurred while loading the content.

Multiple Rows to Multiple Columns - HOW??!?!

Expand Messages
  • Sergio Coelho Charrua
    Hi folks! i ve got this tables: products ... id ref ps_properties ... id id_products name features ... id id_products id_featues_tag id_language value
    Message 1 of 7 , Sep 1, 2004
    • 0 Attachment
      Hi folks!
       
      i've got this tables:
       
      products
      ------------
      id
      ref
       
      ps_properties
      --------------------
      id
      id_products
      name
       
      features
      --------------
      id
      id_products
      id_featues_tag
      id_language
      value
       
      features_tag
      ------------------
      id
      ref
      description
      id_language
       
      languages
      --------------
      id
      ref
       
       
      All columns' name that has "id" in it are Identifiers, eg: ps_properties.id_product = products.id
       
      Here is the problem:
      I need to list ALL products and their features. Ok , this is simple, just do something like:
       
      SELECT DISTINCT products.ref,
      ps_properties.name,
      products.id AS prodid ,
      features.value AS plot,
      features_1.value AS floor,
      features_2.value AS project,
      features_3.value AS area
      FROM products
      INNER JOIN ps_properties ON products.id = ps_properties.id_product AND ps_properties.online=1
      INNER JOIN languages ON ps_properties.id_language = languages.id AND languages.language='PT' 
      INNER JOIN features ON ps_properties.id_product = features.id_product
      INNER JOIN features_tag ON features.id_features_tag = features_tag.id AND features_tag.ref='PLOT'  
      INNER JOIN features features_1 ON ps_properties.id_product = features_1.id_product
      INNER JOIN features_tag features_tag_1 ON features_1.id_features_tag = features_tag_1.id AND features_tag_1.ref='FL'  
      INNER JOIN features features_2 ON ps_properties.id_product = features_2.id_product AND features_2.value='True'
      INNER JOIN features_tag features_tag_2 ON features_2.id_features_tag = features_tag_2.id AND features_tag_2.ref='CPO'  
      INNER JOIN features features_3 ON ps_properties.id_product = features_3.id_product
      INNER JOIN features_tag features_tag_3 ON features_3.id_features_tag = features_tag_3.id AND features_tag_3.ref='AREA'
      This returns me all rows that i need
      ref                |     name         |    prodid    |    plot    |    floor    |    project    |    area    |
      Loja 1 - PO         NULL                 14                                          True         270,45
      LJ6-L4-PO         Loja 6                 91            4         -1 e -2           True         529,00
      loja1                 Loja Ao Sol         5             2G             1             True             52
       
      BUT this returns me what i need only if ALL INNER JOINS are True (or in this case where the inner join returns some value)
      But not all products have features. And i want them listed too. In this case, it should return me about 200 different products.
       
      So i tryed LEFT JOINS, RIGHT JOINS, All kind of joins but they just multiply the returned rows, and i don't want that at all....
      Here is what i get: (about 7442 different rows!!! so i will only show for 1 product only)
      ref                |     name         |    prodid    |    plot    |    floor    |    project    |    area    |
      6A019                 NULL             17                                           True 
      6A019                 NULL             17                                           True            58,30
      6A019                NULL              17                              0            True           52,20  
      6A019                 NULL             17                              0             True         False
       
      note the diferent area value for the same product?? it looks like SQL is messing values...
       
       
      Question is: how can i return all product with their features, even if the product has no features, and without multiplying the rows
      and messing the result set??
      Note that as i save all product's feature values in a  single table i need to self join the Features table. Or is there any other way
      to return all feature rows as columns?
       
      Thanks in advance!
       
      Sergio Coelho Charrua
      Flesk Produgues Digitais Lda
      WebDevelopment Team
      @ :
      info@...
      url : www.flesk.com / www.dominios.pt / www.flesk.net
      Portugal
       
      Want to optimize your viewstate? Accelerate your web application? www.flesk.net has the solution!
       
       
       
       
    • Brett
      Bit of a mess, an so many joins. If you could design the query so I can see the relationship better, where there is a feature for each row, I can show you an
      Message 2 of 7 , Sep 2, 2004
      • 0 Attachment
        Bit of a mess, an so many joins. If you could design the query so I
        can see the relationship better, where there is a feature for each
        row, I can show you an example on diplaying the features in columns
        using a case statement (assuming your using MS SQL). I guess it
        would be a tag and a feature for each row, I don't exactly get your
        schema, so it's a little difficult.

        Brett

        --- In SQLQueriesNoCode@yahoogroups.com, "Sergio Coelho Charrua"
        <scoelho@f...> wrote:
        > Hi folks!
        >
        > i've got this tables:
        >
        > products
        > ------------
        > id
        > ref
        >
        > ps_properties
        > --------------------
        > id
        > id_products
        > name
        >
        > features
        > --------------
        > id
        > id_products
        > id_featues_tag
        > id_language
        > value
        >
        > features_tag
        > ------------------
        > id
        > ref
        > description
        > id_language
        >
        > languages
        > --------------
        > id
        > ref
        >
        >
        > All columns' name that has "id" in it are Identifiers, eg:
        ps_properties.id_product = products.id
        >
        > Here is the problem:
        > I need to list ALL products and their features. Ok , this is
        simple, just do something like:
        >
        > SELECT DISTINCT products.ref,
        > ps_properties.name,
        > products.id AS prodid ,
        > features.value AS plot,
        > features_1.value AS floor,
        > features_2.value AS project,
        > features_3.value AS area
        > FROM products
        > INNER JOIN ps_properties ON products.id = ps_properties.id_product
        AND ps_properties.online=1
        > INNER JOIN languages ON ps_properties.id_language = languages.id
        AND languages.language='PT'
        > INNER JOIN features ON ps_properties.id_product =
        features.id_product
        > INNER JOIN features_tag ON features.id_features_tag =
        features_tag.id AND features_tag.ref='PLOT'
        > INNER JOIN features features_1 ON ps_properties.id_product =
        features_1.id_product
        > INNER JOIN features_tag features_tag_1 ON
        features_1.id_features_tag = features_tag_1.id AND
        features_tag_1.ref='FL'
        > INNER JOIN features features_2 ON ps_properties.id_product =
        features_2.id_product AND features_2.value='True'
        > INNER JOIN features_tag features_tag_2 ON
        features_2.id_features_tag = features_tag_2.id AND
        features_tag_2.ref='CPO'
        > INNER JOIN features features_3 ON ps_properties.id_product =
        features_3.id_product
        > INNER JOIN features_tag features_tag_3 ON
        features_3.id_features_tag = features_tag_3.id AND
        features_tag_3.ref='AREA'
        >
        > This returns me all rows that i need
        > ref | name | prodid | plot
        | floor | project | area |
        > Loja 1 - PO NULL
        14 True 270,45
        > LJ6-L4-PO Loja 6 91 4 -
        1 e -2 True 529,00
        > loja1 Loja Ao Sol 5
        2G 1 True 52
        >
        >
        > BUT this returns me what i need only if ALL INNER JOINS are True
        (or in this case where the inner join returns some value)
        > But not all products have features. And i want them listed too. In
        this case, it should return me about 200 different products.
        >
        > So i tryed LEFT JOINS, RIGHT JOINS, All kind of joins but they
        just multiply the returned rows, and i don't want that at all....
        > Here is what i get: (about 7442 different rows!!! so i will only
        show for 1 product only)
        > ref | name | prodid | plot
        | floor | project | area |
        > 6A019 NULL
        17 True
        > 6A019 NULL
        17 True 58,30
        > 6A019 NULL
        17 0 True 52,20
        > 6A019 NULL
        17 0 True False
        >
        >
        > note the diferent area value for the same product?? it looks like
        SQL is messing values...
        >
        >
        > Question is: how can i return all product with their features,
        even if the product has no features, and without multiplying the rows
        > and messing the result set??
        > Note that as i save all product's feature values in a single
        table i need to self join the Features table. Or is there any other
        way
        > to return all feature rows as columns?
        >
        > Thanks in advance!
        >
        > Sergio Coelho Charrua
        > Flesk Produgues Digitais Lda
        > WebDevelopment Team
        > @ : info@f...
        > url : www.flesk.com / www.dominios.pt / www.flesk.net
        > Portugal
        >
        > Want to optimize your viewstate? Accelerate your web application?
        www.flesk.net has the solution!
      • Jeffrey Schoolcraft
        Are you asking how to transpose rows to columns (if you re looking at your grid, basically rotating it 90 degrees clockwise?) On Wed, 1 Sep 2004 17:19:34
        Message 3 of 7 , Sep 3, 2004
        • 0 Attachment
          Are you asking how to transpose rows to columns (if you're looking at
          your grid, basically rotating it 90 degrees clockwise?)

          On Wed, 1 Sep 2004 17:19:34 +0100, Sergio Coelho Charrua
          <scoelho@...> wrote:
          >
          > Hi folks!
          >
          > i've got this tables:
          >
          > products
          > ------------
          > id
          > ref
          >
          > ps_properties
          > --------------------
          > id
          > id_products
          > name
          >
          > features
          > --------------
          > id
          > id_products
          > id_featues_tag
          > id_language
          > value
          >
          > features_tag
          > ------------------
          > id
          > ref
          > description
          > id_language
          >
          > languages
          > --------------
          > id
          > ref
          >
          >
          > All columns' name that has "id" in it are Identifiers, eg: ps_properties.id_product = products.id
          >
          > Here is the problem:
          > I need to list ALL products and their features. Ok , this is simple, just do something like:
          >
          > SELECT DISTINCT products.ref,
          > ps_properties.name,
          > products.id AS prodid ,
          > features.value AS plot,
          > features_1.value AS floor,
          > features_2.value AS project,
          > features_3.value AS area
          > FROM products
          > INNER JOIN ps_properties ON products.id = ps_properties.id_product AND ps_properties.online=1
          > INNER JOIN languages ON ps_properties.id_language = languages.id AND languages.language='PT'
          > INNER JOIN features ON ps_properties.id_product = features.id_product
          > INNER JOIN features_tag ON features.id_features_tag = features_tag.id AND features_tag.ref='PLOT'
          > INNER JOIN features features_1 ON ps_properties.id_product = features_1.id_product
          > INNER JOIN features_tag features_tag_1 ON features_1.id_features_tag = features_tag_1.id AND features_tag_1.ref='FL'
          > INNER JOIN features features_2 ON ps_properties.id_product = features_2.id_product AND features_2.value='True'
          > INNER JOIN features_tag features_tag_2 ON features_2.id_features_tag = features_tag_2.id AND features_tag_2.ref='CPO'
          > INNER JOIN features features_3 ON ps_properties.id_product = features_3.id_product
          > INNER JOIN features_tag features_tag_3 ON features_3.id_features_tag = features_tag_3.id AND features_tag_3.ref='AREA'
          >
          > This returns me all rows that i need
          > ref | name | prodid | plot | floor | project | area |
          > Loja 1 - PO NULL 14 True 270,45
          > LJ6-L4-PO Loja 6 91 4 -1 e -2 True 529,00
          > loja1 Loja Ao Sol 5 2G 1 True 52
          >
          >
          > BUT this returns me what i need only if ALL INNER JOINS are True (or in this case where the inner join returns some value)
          > But not all products have features. And i want them listed too. In this case, it should return me about 200 different products.
          >
          > So i tryed LEFT JOINS, RIGHT JOINS, All kind of joins but they just multiply the returned rows, and i don't want that at all....
          > Here is what i get: (about 7442 different rows!!! so i will only show for 1 product only)
          >
          > ref | name | prodid | plot | floor | project | area |
          > 6A019 NULL 17 True
          > 6A019 NULL 17 True 58,30
          > 6A019 NULL 17 0 True 52,20
          > 6A019 NULL 17 0 True False
          >
          >
          > note the diferent area value for the same product?? it looks like SQL is messing values...
          >
          >
          > Question is: how can i return all product with their features, even if the product has no features, and without multiplying the rows
          > and messing the result set??
          > Note that as i save all product's feature values in a single table i need to self join the Features table. Or is there any other way
          > to return all feature rows as columns?
          >
          > Thanks in advance!
          >
          > Sergio Coelho Charrua
          > Flesk Produgues Digitais Lda
          > WebDevelopment Team
          > @ : info@...
          > url : www.flesk.com / www.dominios.pt / www.flesk.net
          > Portugal
          >
          > Want to optimize your viewstate? Accelerate your web application? www.flesk.net has the solution!
          >
          >
          >
          >
          >
          >
          > Yahoo! Groups Sponsor
          >
          > ADVERTISEMENT
          >
          >
          > ________________________________
          > Yahoo! Groups Links
          >
          > To visit your group on the web, go to:
          > http://groups.yahoo.com/group/SQLQueriesNoCode/
          >
          > To unsubscribe from this group, send an email to:
          > SQLQueriesNoCode-unsubscribe@yahoogroups.com
          >
          > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
        • Sergio Coelho Charrua
          Hi thanks everyone for your help. I think i solved the problem: i ll have to make (using .NET) a DataTable and fill in the rows with data that i return from
          Message 4 of 7 , Sep 3, 2004
          • 0 Attachment
            Hi

            thanks everyone for your help.

            I think i solved the problem:
            i'll have to make (using .NET) a DataTable and fill in the rows with data
            that i return from several queries within a loop....
            Probably not the best way, but i works


            Thanks once again,

            Sergio Coelho Charrua
            Flesk Produgues Digitais Lda
            WebDevelopment Team
            @ : info@...
            url : www.flesk.com / www.dominios.pt / www.flesk.net
            Portugal

            Want to optimize your viewstate? Accelerate your web application?
            www.flesk.net has the solution!

            ----- Original Message -----
            From: "Jeffrey Schoolcraft" <jeffrey.schoolcraft@...>
            To: <sqlqueriesnocode@yahoogroups.com>
            Sent: Friday, September 03, 2004 12:05 PM
            Subject: Re: [SQLQueriesNoCode] Multiple Rows to Multiple Columns - HOW??!?!


            > Are you asking how to transpose rows to columns (if you're looking at
            > your grid, basically rotating it 90 degrees clockwise?)
            >
            > On Wed, 1 Sep 2004 17:19:34 +0100, Sergio Coelho Charrua
            > <scoelho@...> wrote:
            > >
            > > Hi folks!
            > >
            > > i've got this tables:
            > >
            > > products
            > > ------------
            > > id
            > > ref
            > >
            > > ps_properties
            > > --------------------
            > > id
            > > id_products
            > > name
            > >
            > > features
            > > --------------
            > > id
            > > id_products
            > > id_featues_tag
            > > id_language
            > > value
            > >
            > > features_tag
            > > ------------------
            > > id
            > > ref
            > > description
            > > id_language
            > >
            > > languages
            > > --------------
            > > id
            > > ref
            > >
            > >
            > > All columns' name that has "id" in it are Identifiers, eg:
            ps_properties.id_product = products.id
            > >
            > > Here is the problem:
            > > I need to list ALL products and their features. Ok , this is simple,
            just do something like:
            > >
            > > SELECT DISTINCT products.ref,
            > > ps_properties.name,
            > > products.id AS prodid ,
            > > features.value AS plot,
            > > features_1.value AS floor,
            > > features_2.value AS project,
            > > features_3.value AS area
            > > FROM products
            > > INNER JOIN ps_properties ON products.id = ps_properties.id_product AND
            ps_properties.online=1
            > > INNER JOIN languages ON ps_properties.id_language = languages.id AND
            languages.language='PT'
            > > INNER JOIN features ON ps_properties.id_product = features.id_product
            > > INNER JOIN features_tag ON features.id_features_tag = features_tag.id
            AND features_tag.ref='PLOT'
            > > INNER JOIN features features_1 ON ps_properties.id_product =
            features_1.id_product
            > > INNER JOIN features_tag features_tag_1 ON features_1.id_features_tag =
            features_tag_1.id AND features_tag_1.ref='FL'
            > > INNER JOIN features features_2 ON ps_properties.id_product =
            features_2.id_product AND features_2.value='True'
            > > INNER JOIN features_tag features_tag_2 ON features_2.id_features_tag =
            features_tag_2.id AND features_tag_2.ref='CPO'
            > > INNER JOIN features features_3 ON ps_properties.id_product =
            features_3.id_product
            > > INNER JOIN features_tag features_tag_3 ON features_3.id_features_tag =
            features_tag_3.id AND features_tag_3.ref='AREA'
            > >
            > > This returns me all rows that i need
            > > ref | name | prodid | plot |
            floor | project | area |
            > > Loja 1 - PO NULL 14
            True 270,45
            > > LJ6-L4-PO Loja 6 91 4 -1 e -2
            True 529,00
            > > loja1 Loja Ao Sol 5 2G 1
            True 52
            > >
            > >
            > > BUT this returns me what i need only if ALL INNER JOINS are True (or in
            this case where the inner join returns some value)
            > > But not all products have features. And i want them listed too. In this
            case, it should return me about 200 different products.
            > >
            > > So i tryed LEFT JOINS, RIGHT JOINS, All kind of joins but they just
            multiply the returned rows, and i don't want that at all....
            > > Here is what i get: (about 7442 different rows!!! so i will only show
            for 1 product only)
            > >
            > > ref | name | prodid | plot |
            floor | project | area |
            > > 6A019 NULL 17
            True
            > > 6A019 NULL 17
            True 58,30
            > > 6A019 NULL 17 0
            True 52,20
            > > 6A019 NULL 17 0
            True False
            > >
            > >
            > > note the diferent area value for the same product?? it looks like SQL is
            messing values...
            > >
            > >
            > > Question is: how can i return all product with their features, even if
            the product has no features, and without multiplying the rows
            > > and messing the result set??
            > > Note that as i save all product's feature values in a single table i
            need to self join the Features table. Or is there any other way
            > > to return all feature rows as columns?
            > >
            > > Thanks in advance!
            > >
            > > Sergio Coelho Charrua
            > > Flesk Produgues Digitais Lda
            > > WebDevelopment Team
            > > @ : info@...
            > > url : www.flesk.com / www.dominios.pt / www.flesk.net
            > > Portugal
            > >
            > > Want to optimize your viewstate? Accelerate your web application?
            www.flesk.net has the solution!
            > >
            > >
            > >
            > >
            > >
            > >
            > > Yahoo! Groups Sponsor
            > >
            > > ADVERTISEMENT
            > >
            > >
            > > ________________________________
            > > Yahoo! Groups Links
            > >
            > > To visit your group on the web, go to:
            > > http://groups.yahoo.com/group/SQLQueriesNoCode/
            > >
            > > To unsubscribe from this group, send an email to:
            > > SQLQueriesNoCode-unsubscribe@yahoogroups.com
            > >
            > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
            >
            >
            >
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
            >
            >
          • Brett
            Yes, you can do it in code, but in can be achived in SQL. ... with data ... HOW??!?! ... looking at ... simple, ... ps_properties.id_product AND ...
            Message 5 of 7 , Sep 3, 2004
            • 0 Attachment
              Yes, you can do it in code, but in can be achived in SQL.


              --- In SQLQueriesNoCode@yahoogroups.com, "Sergio Coelho Charrua"
              <scoelho@f...> wrote:
              > Hi
              >
              > thanks everyone for your help.
              >
              > I think i solved the problem:
              > i'll have to make (using .NET) a DataTable and fill in the rows
              with data
              > that i return from several queries within a loop....
              > Probably not the best way, but i works
              >
              >
              > Thanks once again,
              >
              > Sergio Coelho Charrua
              > Flesk Produgues Digitais Lda
              > WebDevelopment Team
              > @ : info@f...
              > url : www.flesk.com / www.dominios.pt / www.flesk.net
              > Portugal
              >
              > Want to optimize your viewstate? Accelerate your web application?
              > www.flesk.net has the solution!
              >
              > ----- Original Message -----
              > From: "Jeffrey Schoolcraft" <jeffrey.schoolcraft@g...>
              > To: <sqlqueriesnocode@yahoogroups.com>
              > Sent: Friday, September 03, 2004 12:05 PM
              > Subject: Re: [SQLQueriesNoCode] Multiple Rows to Multiple Columns -
              HOW??!?!
              >
              >
              > > Are you asking how to transpose rows to columns (if you're
              looking at
              > > your grid, basically rotating it 90 degrees clockwise?)
              > >
              > > On Wed, 1 Sep 2004 17:19:34 +0100, Sergio Coelho Charrua
              > > <scoelho@f...> wrote:
              > > >
              > > > Hi folks!
              > > >
              > > > i've got this tables:
              > > >
              > > > products
              > > > ------------
              > > > id
              > > > ref
              > > >
              > > > ps_properties
              > > > --------------------
              > > > id
              > > > id_products
              > > > name
              > > >
              > > > features
              > > > --------------
              > > > id
              > > > id_products
              > > > id_featues_tag
              > > > id_language
              > > > value
              > > >
              > > > features_tag
              > > > ------------------
              > > > id
              > > > ref
              > > > description
              > > > id_language
              > > >
              > > > languages
              > > > --------------
              > > > id
              > > > ref
              > > >
              > > >
              > > > All columns' name that has "id" in it are Identifiers, eg:
              > ps_properties.id_product = products.id
              > > >
              > > > Here is the problem:
              > > > I need to list ALL products and their features. Ok , this is
              simple,
              > just do something like:
              > > >
              > > > SELECT DISTINCT products.ref,
              > > > ps_properties.name,
              > > > products.id AS prodid ,
              > > > features.value AS plot,
              > > > features_1.value AS floor,
              > > > features_2.value AS project,
              > > > features_3.value AS area
              > > > FROM products
              > > > INNER JOIN ps_properties ON products.id =
              ps_properties.id_product AND
              > ps_properties.online=1
              > > > INNER JOIN languages ON ps_properties.id_language =
              languages.id AND
              > languages.language='PT'
              > > > INNER JOIN features ON ps_properties.id_product =
              features.id_product
              > > > INNER JOIN features_tag ON features.id_features_tag =
              features_tag.id
              > AND features_tag.ref='PLOT'
              > > > INNER JOIN features features_1 ON ps_properties.id_product =
              > features_1.id_product
              > > > INNER JOIN features_tag features_tag_1 ON
              features_1.id_features_tag =
              > features_tag_1.id AND features_tag_1.ref='FL'
              > > > INNER JOIN features features_2 ON ps_properties.id_product =
              > features_2.id_product AND features_2.value='True'
              > > > INNER JOIN features_tag features_tag_2 ON
              features_2.id_features_tag =
              > features_tag_2.id AND features_tag_2.ref='CPO'
              > > > INNER JOIN features features_3 ON ps_properties.id_product =
              > features_3.id_product
              > > > INNER JOIN features_tag features_tag_3 ON
              features_3.id_features_tag =
              > features_tag_3.id AND features_tag_3.ref='AREA'
              > > >
              > > > This returns me all rows that i need
              > > > ref | name | prodid |
              plot |
              > floor | project | area |
              > > > Loja 1 - PO NULL 14
              > True 270,45
              > > > LJ6-L4-PO Loja 6 91
              4 -1 e -2
              > True 529,00
              > > > loja1 Loja Ao Sol 5
              2G 1
              > True 52
              > > >
              > > >
              > > > BUT this returns me what i need only if ALL INNER JOINS are
              True (or in
              > this case where the inner join returns some value)
              > > > But not all products have features. And i want them listed
              too. In this
              > case, it should return me about 200 different products.
              > > >
              > > > So i tryed LEFT JOINS, RIGHT JOINS, All kind of joins but they
              just
              > multiply the returned rows, and i don't want that at all....
              > > > Here is what i get: (about 7442 different rows!!! so i will
              only show
              > for 1 product only)
              > > >
              > > > ref | name | prodid |
              plot |
              > floor | project | area |
              > > > 6A019 NULL 17
              > True
              > > > 6A019 NULL 17
              > True 58,30
              > > > 6A019 NULL
              17 0
              > True 52,20
              > > > 6A019 NULL
              17 0
              > True False
              > > >
              > > >
              > > > note the diferent area value for the same product?? it looks
              like SQL is
              > messing values...
              > > >
              > > >
              > > > Question is: how can i return all product with their features,
              even if
              > the product has no features, and without multiplying the rows
              > > > and messing the result set??
              > > > Note that as i save all product's feature values in a single
              table i
              > need to self join the Features table. Or is there any other way
              > > > to return all feature rows as columns?
              > > >
              > > > Thanks in advance!
              > > >
              > > > Sergio Coelho Charrua
              > > > Flesk Produgues Digitais Lda
              > > > WebDevelopment Team
              > > > @ : info@f...
              > > > url : www.flesk.com / www.dominios.pt / www.flesk.net
              > > > Portugal
              > > >
              > > > Want to optimize your viewstate? Accelerate your web
              application?
              > www.flesk.net has the solution!
              > > >
              > > >
              > > >
              > > >
              > > >
              > > >
              > > > Yahoo! Groups Sponsor
              > > >
              > > > ADVERTISEMENT
              > > >
              > > >
              > > > ________________________________
              > > > Yahoo! Groups Links
              > > >
              > > > To visit your group on the web, go to:
              > > > http://groups.yahoo.com/group/SQLQueriesNoCode/
              > > >
              > > > To unsubscribe from this group, send an email to:
              > > > SQLQueriesNoCode-unsubscribe@yahoogroups.com
              > > >
              > > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
              Service.
              > >
              > >
              > >
              > >
              > > Yahoo! Groups Links
              > >
              > >
              > >
              > >
              > >
              > >
            • liston
              good moring all, i m stuck in a problem. i want to insert some records from one table to another table. The two of this have relationship ship PK--FK. errors
              Message 6 of 7 , Sep 10, 2004
              • 0 Attachment
                good moring all,

                i'm stuck in a problem. i want to insert some records from one table to
                another table. The two of this have relationship ship PK--FK. errors
                appear like:
                Microsoft OLE DB Provider for SQL Server (0x80004005)
                Subquery returned more than 1 value. This is not permitted when the
                subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
                expression.

                my code in ASP and i use MS. SQL server 2000 is:

                set ceknomin=server.CreateObject("ADODB.Recordset")
                ceknumin.open "select min(id) as numinimum from temporder where
                nuorder="&trim(request.form(inputorder)),dbconn,1,2
                beginnu=ceknumin("numinimum")
                ceknumin.close
                set ceknumin=nothing
                set ceksum=server.CreateObject("ADODB.Recordset")
                ceksum.open "select max(id) from temppesanan where
                nuorder="&trim(request.form(inputorder)),dbconn,1,2
                maxnu=ceksum.recordcount
                ceksum.close
                set ceksum=nothing
                for i=beginnu to (=beginnuto +maxnu) step 1
                set tablesource=server.CreateObject("ADODB.Recordset")
                sqlsource="select * from temporder where id="&i
                tablesource.open sqlsource,dbconn,1,2
                while not tablesource.eof
                inputsql="insert into
                realorder(nuorder,inputcode,sumorder)
                values("&trim(tablesource("nuorder"))&",'"&trim(tablesource("inputcode"))&"',"&trim(tablesource("sumorder"))&")"
                dbconn.execute(inputsql)
                tablesource.movenext
                wend
                tablesource.close
                set tablesource=nothing
                next

                i'm very hope and thanks for help me

                Liston



                Liston Sitorus <liston@...>
                Database Programmer
                Database & statistik
                Puskom Unri
              • umesh Baboo
                Hi The code which u r wrote it s correct, there is no problum but when u r passing value to this page 1st check that value is passing properly or not and the
                Message 7 of 7 , Sep 20, 2004
                • 0 Attachment
                  Hi
                   
                     The code which u r wrote it's correct, there is no problum but when u r passing value to this page 1st check that value is passing properly or not and the value which u r trying to retrive from database it's exists or not.
                   
                  Bye


                  liston <liston@...> wrote:
                  good moring all,

                  i'm stuck in a problem.  i want to insert some records from one table to
                  another table. The two of this have relationship ship PK--FK.  errors
                  appear like:
                  Microsoft OLE DB Provider for SQL Server (0x80004005)
                  Subquery returned more than 1 value. This is not permitted when the
                  subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
                  expression.

                  my code in ASP  and i  use MS. SQL server 2000 is:

                  set ceknomin=server.CreateObject("ADODB.Recordset")
                  ceknumin.open "select min(id) as numinimum from temporder where
                  nuorder="&trim(request.form(inputorder)),dbconn,1,2
                  beginnu=ceknumin("numinimum")
                  ceknumin.close
                  set ceknumin=nothing
                  set ceksum=server.CreateObject("ADODB.Recordset")
                  ceksum.open "select max(id) from temppesanan where
                  nuorder="&trim(request.form(inputorder)),dbconn,1,2
                  maxnu=ceksum.recordcount
                  ceksum.close
                  set ceksum=nothing
                  for i=beginnu to (=beginnuto +maxnu) step 1
                     set tablesource=server.CreateObject("ADODB.Recordset")
                     sqlsource="select * from temporder where id="&i
                         tablesource.open sqlsource,dbconn,1,2
                                 while not tablesource.eof
                                 inputsql="insert into
                  realorder(nuorder,inputcode,sumorder)
                  values("&trim(tablesource("nuorder"))&",'"&trim(tablesource("inputcode"))&"',"&trim(tablesource("sumorder"))&")"
                                 dbconn.execute(inputsql)
                                 tablesource.movenext
                                 wend
                         tablesource.close
                     set tablesource=nothing
                  next

                  i'm very hope and thanks for help me

                  Liston


                    
                  Liston Sitorus <liston@...>
                  Database Programmer
                  Database & statistik
                  Puskom Unri


                  Do you Yahoo!?
                  New and Improved Yahoo! Mail - 100MB free storage!

                Your message has been successfully submitted and would be delivered to recipients shortly.