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

Sql Help - Exclusion Query?

Expand Messages
  • Ronda K
    Cross Posting Can someone help ... I have a product style table (the actual query joins to about 4 different tables but I am going to try to simplify it here:
    Message 1 of 5 , Oct 24, 2012
    • 0 Attachment
      Cross Posting


      Can someone help ...

      I have a product style table (the actual query joins to about 4 different
      tables but I am going to try to simplify it here:

      Styles Table
      -------------
      ProductStyle Field

      Example Data:

      abc
      abd
      abe
      abc2
      abc3

      I only want to show abc,abd & abe

      I have another table

      ProductstyleLookup Table
      -PrimaryStyle
      -SecondaryStyle

      the data would look like this (abc would repeat for every deviant version)
      abc
      abc2

      abc
      abc3

      When I query I only want to show this data>>> abc,abd & abe

      So how would I join to that table to exclude any item in the secondary
      column - I know I can do a where not in but in DB2 I can use an EXCLUSION
      join ...

      And John or Arnie ;) (or any other SQL gurus) - I am in the design phase of
      this extra table so if you see a better way to manage it please let me know.

      It is used for a product style with enhancements so it actually has a SKU
      but I don't want to show it to my visitor I want to do the work for them so
      for instance if I have a coverall that is named 62371 I might have another
      style that is 62371E which is already priced for custom embroidery or
      62371L which means I am going to him it... all they want to see if the
      62371 and then I will give them options to customize and then look up the
      appropriate sku based on the options chosen.

      TIA!!


      [Non-text portions of this message have been removed]
    • Farhan Ahmed
      In SQL Server there is keyword “except” which is similar to you defined in DB2. I have not used DB2 so that’s why I don’t know how Exclusion work and
      Message 2 of 5 , Oct 24, 2012
      • 0 Attachment
        In SQL Server there is keyword �except� which is similar to you defined in DB2. I have not used DB2 so that�s why I don�t know how Exclusion work and its performance.

        Please check the following example and let me know if this is exactly what you required

        Declare @Style Table (ProductStyle varchar(10))
        Declare @ProdStyle Table (PrimaryStyle Varchar(10),SecondryStyle Varchar(10))

        Insert into @style
        Select 'abc'
        union all
        Select 'abd'
        union all
        Select 'abe'
        union all
        Select 'abc2'
        union all
        Select 'abc3'

        Insert into @ProdStyle
        Select 'abc','abc2'
        union all
        select 'abc','abc3'

        Select * from @style where ProductStyle in(
        Select ProductStyle from @style a
        except
        Select SecondryStyle from @ProdStyle b
        )



        _________________________________________________________________________________________________________
        Regards,

        Farhan Ahmed
        Programmer Analyst
        The Shams Group
        Karachi, Pakistan
        (+92) 345 2523688
        View my Certifications







        To: SQLQueriesNoCode@yahoogroups.com
        From: rka@...
        Date: Wed, 24 Oct 2012 15:09:57 -0500
        Subject: [SQLQueriesNoCode] Sql Help - Exclusion Query?





        Cross Posting

        Can someone help ...

        I have a product style table (the actual query joins to about 4 different
        tables but I am going to try to simplify it here:

        Styles Table
        -------------
        ProductStyle Field

        Example Data:

        abc
        abd
        abe
        abc2
        abc3

        I only want to show abc,abd & abe

        I have another table

        ProductstyleLookup Table
        -PrimaryStyle
        -SecondaryStyle

        the data would look like this (abc would repeat for every deviant version)
        abc
        abc2

        abc
        abc3

        When I query I only want to show this data>>> abc,abd & abe

        So how would I join to that table to exclude any item in the secondary
        column - I know I can do a where not in but in DB2 I can use an EXCLUSION
        join ...

        And John or Arnie ;) (or any other SQL gurus) - I am in the design phase of
        this extra table so if you see a better way to manage it please let me know.

        It is used for a product style with enhancements so it actually has a SKU
        but I don't want to show it to my visitor I want to do the work for them so
        for instance if I have a coverall that is named 62371 I might have another
        style that is 62371E which is already priced for custom embroidery or
        62371L which means I am going to him it... all they want to see if the
        62371 and then I will give them options to customize and then look up the
        appropriate sku based on the options chosen.

        TIA!!

        [Non-text portions of this message have been removed]






        [Non-text portions of this message have been removed]
      • Paul Livengood
        Ronda; If I understand your requirements you want to list all items in Styles Table where ProductStyle Column is not in the SecondaryStyle Column of the
        Message 3 of 5 , Oct 24, 2012
        • 0 Attachment
          Ronda;
          If I understand your requirements you want to list all items in Styles Table where ProductStyle Column is not in the SecondaryStyle Column of the ProductStyleLookup table. correct?
           
          You can either do a Outer Join (Left or Right) or use NOT IN.  Because you said the query is really more complicated, and it seems you do not need information from the ProductStyleLookup table, I would recommend the NOT IN statement.  It keeps things easier to read and usually runs just as fast (provided indexes are setup).
           
          As for this being the best (most optimal) setup it is hard to say without know more about the system requirements.  In general I might suggest de-normalizing your database and putting a field in the Styles Table that would help with this type of lookup. 
          You could use any of the following;
           - a Primary Product field.  so for 62371 is would be null (or empty string depending on your dev standards).  Then 62371E, 62371L, and 62371W would have 62371 as the Primary Product field.  
           - a Style type field (or table if one Style could be multiple types) that would like if it is a Primary or Enhancements SKU. 
           
           
          Just thoughts.
           
          HTH
          Paul
           
           


          ________________________________
          From: Ronda K <rka@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Wednesday, October 24, 2012 2:09 PM
          Subject: [SQLQueriesNoCode] Sql Help - Exclusion Query?

           

          Cross Posting

          Can someone help ...

          I have a product style table (the actual query joins to about 4 different
          tables but I am going to try to simplify it here:

          Styles Table
          -------------
          ProductStyle Field

          Example Data:

          abc
          abd
          abe
          abc2
          abc3

          I only want to show abc,abd & abe

          I have another table

          ProductstyleLookup Table
          -PrimaryStyle
          -SecondaryStyle

          the data would look like this (abc would repeat for every deviant version)
          abc
          abc2

          abc
          abc3

          When I query I only want to show this data>>> abc,abd & abe

          So how would I join to that table to exclude any item in the secondary
          column - I know I can do a where not in but in DB2 I can use an EXCLUSION
          join ...

          And John or Arnie ;) (or any other SQL gurus) - I am in the design phase of
          this extra table so if you see a better way to manage it please let me know.

          It is used for a product style with enhancements so it actually has a SKU
          but I don't want to show it to my visitor I want to do the work for them so
          for instance if I have a coverall that is named 62371 I might have another
          style that is 62371E which is already priced for custom embroidery or
          62371L which means I am going to him it... all they want to see if the
          62371 and then I will give them options to customize and then look up the
          appropriate sku based on the options chosen.

          TIA!!

          [Non-text portions of this message have been removed]




          [Non-text portions of this message have been removed]
        • Ronda K
          Paul, Thanks for your response. I did end up using a where not in statement, was just curious if there was an SQL equivalent to the DB2 (as400) exception or
          Message 4 of 5 , Oct 24, 2012
          • 0 Attachment
            Paul,
            Thanks for your response. I did end up using a where not in statement, was
            just curious if there was an SQL equivalent to the DB2 (as400) exception or
            exclusion join (don't recall what it is called exactly)

            As for normalizing - I actually have a little bit more in the second table

            --Primary Style
            --Secondary Style
            --Type of modification (e.g. embroidery, hem addition, hem shortening,
            patch addition)... so I wound up with a table of exception types with a
            code and then carry that code over to the table with the list of exception
            items. I had not thought about carrying it in the products table like you
            suggested but will look at that in terms of flexibility - I just did not
            want to widen that table too much more.

            I will also use this secondary table when pulling the primary style into
            the page to construct the additional options.

            I did post this question on another list and received an interesting
            response that I am going to try - mostly just because it is a suggestion i
            was not aware of.

            Below is the response - utilizing the word EXCEPT (I did not know you could
            do that).

            Thanks again for your reply
            Ronda

            ---------------------POST response follows--------------------

            In SQL Server there is keyword �except� which is similar to you defined in
            DB2. I have not used DB2 so that�s why I don�t know how Exclusion work and
            its performance.

            Please check the following example and let me know if this is exactly what
            you required

            Declare @Style Table (ProductStyle varchar(10))
            Declare @ProdStyle Table (PrimaryStyle Varchar(10),SecondryStyle
            Varchar(10))

            Insert into @style
            Select 'abc'
            union all
            Select 'abd'
            union all
            Select 'abe'
            union all
            Select 'abc2'
            union all
            Select 'abc3'

            Insert into @ProdStyle
            Select 'abc','abc2'
            union all
            select 'abc','abc3'

            Select * from @style where ProductStyle in(
            Select ProductStyle from @style a
            except
            Select SecondryStyle from @ProdStyle b
            )


            On Wed, Oct 24, 2012 at 7:55 PM, Paul Livengood <p_livengood@...>wrote:

            > **
            >
            >
            > Ronda;
            > If I understand your requirements you want to list all items in Styles
            > Table where ProductStyle Column is not in the SecondaryStyle Column of the
            > ProductStyleLookup table. correct?
            >
            > You can either do a Outer Join (Left or Right) or use NOT IN. Because you
            > said the query is really more complicated, and it seems you do not need
            > information from the ProductStyleLookup table, I would recommend the NOT IN
            > statement. It keeps things easier to read and usually runs just as fast
            > (provided indexes are setup).
            >
            > As for this being the best (most optimal) setup it is hard to say without
            > know more about the system requirements. In general I might suggest
            > de-normalizing your database and putting a field in the Styles Table that
            > would help with this type of lookup.
            > You could use any of the following;
            > - a Primary Product field. so for 62371 is would be null (or empty
            > string depending on your dev standards). Then 62371E, 62371L, and 62371W
            > would have 62371 as the Primary Product field.
            > - a Style type field (or table if one Style could be multiple types) that
            > would like if it is a Primary or Enhancements SKU.
            >
            >
            > Just thoughts.
            >
            > HTH
            > Paul
            >
            >
            >
            >
            > ________________________________
            > From: Ronda K <rka@...>
            > To: SQLQueriesNoCode@yahoogroups.com
            > Sent: Wednesday, October 24, 2012 2:09 PM
            > Subject: [SQLQueriesNoCode] Sql Help - Exclusion Query?
            >
            >
            >
            > Cross Posting
            >
            > Can someone help ...
            >
            > I have a product style table (the actual query joins to about 4 different
            > tables but I am going to try to simplify it here:
            >
            > Styles Table
            > -------------
            > ProductStyle Field
            >
            > Example Data:
            >
            > abc
            > abd
            > abe
            > abc2
            > abc3
            >
            > I only want to show abc,abd & abe
            >
            > I have another table
            >
            > ProductstyleLookup Table
            > -PrimaryStyle
            > -SecondaryStyle
            >
            > the data would look like this (abc would repeat for every deviant version)
            > abc
            > abc2
            >
            > abc
            > abc3
            >
            > When I query I only want to show this data>>> abc,abd & abe
            >
            > So how would I join to that table to exclude any item in the secondary
            > column - I know I can do a where not in but in DB2 I can use an EXCLUSION
            > join ...
            >
            > And John or Arnie ;) (or any other SQL gurus) - I am in the design phase of
            > this extra table so if you see a better way to manage it please let me
            > know.
            >
            > It is used for a product style with enhancements so it actually has a SKU
            > but I don't want to show it to my visitor I want to do the work for them so
            > for instance if I have a coverall that is named 62371 I might have another
            > style that is 62371E which is already priced for custom embroidery or
            > 62371L which means I am going to him it... all they want to see if the
            > 62371 and then I will give them options to customize and then look up the
            > appropriate sku based on the options chosen.
            >
            > TIA!!
            >
            > [Non-text portions of this message have been removed]
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >


            [Non-text portions of this message have been removed]
          • Arnie Rowland
            EXCEPT Regards, Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172 You cannot do a kindness too soon because you never know how soon it will be too
            Message 5 of 5 , Oct 24, 2012
            • 0 Attachment
              EXCEPT


              Regards,

              Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172

              "You cannot do a kindness too soon because you never know how soon
              it will be too late."
              -Ralph Waldo Emerson



              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ronda K
              Sent: Wednesday, October 24, 2012 8:34 PM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: Re: Spam Low - Re: [SQLQueriesNoCode] Sql Help - Exclusion
              Query?

              Paul,
              Thanks for your response. I did end up using a where not in statement,
              was just curious if there was an SQL equivalent to the DB2 (as400)
              exception or exclusion join (don't recall what it is called exactly)

              As for normalizing - I actually have a little bit more in the second
              table

              --Primary Style
              --Secondary Style
              --Type of modification (e.g. embroidery, hem addition, hem shortening,
              patch addition)... so I wound up with a table of exception types with a
              code and then carry that code over to the table with the list of
              exception items. I had not thought about carrying it in the products
              table like you suggested but will look at that in terms of flexibility -
              I just did not want to widen that table too much more.

              I will also use this secondary table when pulling the primary style into
              the page to construct the additional options.

              I did post this question on another list and received an interesting
              response that I am going to try - mostly just because it is a suggestion
              i was not aware of.

              Below is the response - utilizing the word EXCEPT (I did not know you
              could do that).

              Thanks again for your reply
              Ronda

              ---------------------POST response follows--------------------

              In SQL Server there is keyword "except" which is similar to you defined
              in DB2. I have not used DB2 so that's why I don't know how Exclusion
              work and its performance.

              Please check the following example and let me know if this is exactly
              what you required

              Declare @Style Table (ProductStyle varchar(10)) Declare @ProdStyle
              Table (PrimaryStyle Varchar(10),SecondryStyle
              Varchar(10))

              Insert into @style
              Select 'abc'
              union all
              Select 'abd'
              union all
              Select 'abe'
              union all
              Select 'abc2'
              union all
              Select 'abc3'

              Insert into @ProdStyle
              Select 'abc','abc2'
              union all
              select 'abc','abc3'

              Select * from @style where ProductStyle in( Select ProductStyle from
              @style a except Select SecondryStyle from @ProdStyle b
              )


              On Wed, Oct 24, 2012 at 7:55 PM, Paul Livengood
              <p_livengood@...>wrote:

              > **
              >
              >
              > Ronda;
              > If I understand your requirements you want to list all items in Styles

              > Table where ProductStyle Column is not in the SecondaryStyle Column of

              > the ProductStyleLookup table. correct?
              >
              > You can either do a Outer Join (Left or Right) or use NOT IN. Because

              > you said the query is really more complicated, and it seems you do not

              > need information from the ProductStyleLookup table, I would recommend
              > the NOT IN statement. It keeps things easier to read and usually runs

              > just as fast (provided indexes are setup).
              >
              > As for this being the best (most optimal) setup it is hard to say
              > without know more about the system requirements. In general I might
              > suggest de-normalizing your database and putting a field in the Styles

              > Table that would help with this type of lookup.
              > You could use any of the following;
              > - a Primary Product field. so for 62371 is would be null (or empty
              > string depending on your dev standards). Then 62371E, 62371L, and
              > 62371W would have 62371 as the Primary Product field.
              > - a Style type field (or table if one Style could be multiple types)
              > that would like if it is a Primary or Enhancements SKU.
              >
              >
              > Just thoughts.
              >
              > HTH
              > Paul
              >
              >
              >
              >
              > ________________________________
              > From: Ronda K <rka@...>
              > To: SQLQueriesNoCode@yahoogroups.com
              > Sent: Wednesday, October 24, 2012 2:09 PM
              > Subject: [SQLQueriesNoCode] Sql Help - Exclusion Query?
              >
              >
              >
              > Cross Posting
              >
              > Can someone help ...
              >
              > I have a product style table (the actual query joins to about 4
              > different tables but I am going to try to simplify it here:
              >
              > Styles Table
              > -------------
              > ProductStyle Field
              >
              > Example Data:
              >
              > abc
              > abd
              > abe
              > abc2
              > abc3
              >
              > I only want to show abc,abd & abe
              >
              > I have another table
              >
              > ProductstyleLookup Table
              > -PrimaryStyle
              > -SecondaryStyle
              >
              > the data would look like this (abc would repeat for every deviant
              > version) abc
              > abc2
              >
              > abc
              > abc3
              >
              > When I query I only want to show this data>>> abc,abd & abe
              >
              > So how would I join to that table to exclude any item in the secondary

              > column - I know I can do a where not in but in DB2 I can use an
              > EXCLUSION join ...
              >
              > And John or Arnie ;) (or any other SQL gurus) - I am in the design
              > phase of this extra table so if you see a better way to manage it
              > please let me know.
              >
              > It is used for a product style with enhancements so it actually has a
              > SKU but I don't want to show it to my visitor I want to do the work
              > for them so for instance if I have a coverall that is named 62371 I
              > might have another style that is 62371E which is already priced for
              > custom embroidery or 62371L which means I am going to him it... all
              > they want to see if the
              > 62371 and then I will give them options to customize and then look up
              > the appropriate sku based on the options chosen.
              >
              > TIA!!
              >
              > [Non-text portions of this message have been removed]
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >


              [Non-text portions of this message have been removed]



              ------------------------------------

              Yahoo! Groups Links
            Your message has been successfully submitted and would be delivered to recipients shortly.