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

Re: Spam Low - Re: [SQLQueriesNoCode] Sql Help - Exclusion Query?

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