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

Re: [SQLQueriesNoCode] Sql Help - Exclusion Query?

Expand Messages
  • 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 1 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 2 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 3 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.