Solved

Sql how to exclude from a table

Posted on 2016-08-24
3
84 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now