Solved

Sql how to exclude from a table

Posted on 2016-08-24
3
107 Views
Last Modified: 2016-08-25
Need some help here. Trying to write a select statement that pulls store/agreement/order information. I want all store and agreement information from AGR_DAILY_BuyerStep table except for those in the manually created exclusion table.


declare @OrgUnits table (OrgunitID typ__ID) --this table holds store info
declare @StoreAgmnt table (OrgunitId typ__id, AgreementId typ__id) --agreements by store to exclude
declare @OrgUnitIds Varchar(max) --store ids
      set @OrgUnitIds = ('10000032-10002125-10002124-10002123-10002120-10002119') -- stores we are using

--exclusion table
insert into @StoreAgmnt
            (OrgunitId, AgreementId)
      VALUES
            (10000032,10000001),
            (10002125,10000004),
            (10002124,10000005),
            (10002123,10000001),
            (10002120,10000004),
            (10002119,10000001)

--accepts store params
insert into @OrgUnits (OrgunitID)
 select item from fn__String2TableEx(@OrgUnitIds,'-')

--select all POIDS except for those in  exclusion table
SELECT distinct(POID)
  FROM AGR_DAILY_BuyerStep a
  --left outer join @StoreAgmnt b on a.BuyerID=b.OrgunitId
  where
      buyerid in (select OrgunitID from @OrgUnits)  
      and NOT EXISTS(  -----this is the part i am not sure of how to exclude?
0
Comment
Question by:jacobJL
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41769215
<knee jerk reaction>
>I want all store and agreement information from AGR_DAILY_BuyerStep table except for those in the manually created exclusion table.
SELECT distinct(POID)
FROM AGR_DAILY_BuyerStep a
   --- The LEFT means all rows in AGR_Daily_BuyerStep are included.. 
   LEFT JOIN @StoreAgmnt b on a.BuyerID=b.OrgunitId
-- The WHERE means now filter out all rows where there is a matching @StoreAgmnt row
WHERE b.OrgunitId IS NULL

Open in new window

1
 

Author Closing Comment

by:jacobJL
ID: 41770532
Thank you, this was close enough. got me on the right path. I had this at one point. the problem was I needed to add a.AgreementId = b.AgreementId  to the  join clause
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41770537
Thanks for the accept.  btw if you do an images.google.com search for 'SQL JOIN' you'll see lots of pretty pictures with T-SQL on how that works.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question