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

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

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