Help with NOT EXISTS

I have been getting some great help from everyone here but I can't get the results of my query that I am looking for.

I UPDATE data in MattersQScheduling MQS with data from ContactsQScheduling CQS.  If there is a new row in CQS then I need to INSERT into MQS.  The INSERT is where I need help. I don't know how to get the rows that are NOT IN MQS.

MattersContacts MC is the table  that links MQS and CQS together.
MC.MattersID = MQS.MattersID
MC.ContactsID = CQS.ContactsID

Matters M table
M.ID
M.AreaOfLaw
M.Status

Contacts C table
C.ID
C.ContactClass


This query returns 3 rows.
select  mc.matters, MQS.Matters
FROM MattersQSCHEDULING MQS
INNER JOIN MattersContacts MC ON mqs.MattersID = MC.MattersID 
INNER JOIN Matters M ON MC.MattersID = M.MattersID 
INNER JOIN CONTACTS C ON MC.contactsID = C.contactsID 
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))

Open in new window



This query returns 4 rows.
select mc.matters,  CQ.*
FROM ContactsQSCHEDULING CQ
INNER JOIN MattersContacts MC ON CQ.contactsID = MC.ContactsID 
INNER JOIN Matters M ON MC.Matters = M.Matters
INNER JOIN CONTACTS C ON mc.contactsID = C.CONTACTSID
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))

Open in new window


There is one more row in the second query and I need to INSERT that data into MQS table.  I don't know how to accomplish this because what I've tried doesn't work.

Here is what I've tried.  This returns nothing.

select  mc.matters, MQS.Matters
FROM MattersQSCHEDULING MQS
INNER JOIN MattersContacts MC ON mqs.MattersID = MC.MattersID 
INNER JOIN Matters M ON MC.MattersID = M.MattersID 
INNER JOIN CONTACTS C ON MC.contactsID = C.contactsID 
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))

                AND NOT EXISTS 
                (
                                select mc.matters,  CQ.*
                                FROM ContactsQSCHEDULING CQ
                                INNER JOIN MattersContacts MC ON CQ.contactsID = MC.ContactsID 
                                INNER JOIN Matters M ON MC.Matters = M.Matters
                                INNER JOIN CONTACTS C ON mc.contactsID = C.CONTACTSID
                                WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		                AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		               AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))
                )

Open in new window

huerita37Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

huerita37Author Commented:
This gives me the result I am looking for.  The problem is that I am using NOT IN.  Shouldn't I use NOT EXISTS?  If so, then how?

select mc.matters
FROM ContactsQSCHEDULING CQ
INNER JOIN MattersContacts MC ON CQ.contacts = MC.Contacts 
INNER JOIN Matters M ON MC.Matters = M.Matters
INNER JOIN CONTACTS C ON mc.contacts = C.CONTACTS
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))
		and c.FullName = 'jacque scott'


		and mc.matters NOT IN (

		
select  mc.matters
FROM MattersQSCHEDULING MQS
INNER JOIN MattersContacts MC ON mqs.Matters = MC.Matters  
INNER JOIN Matters M ON MC.Matters = M.Matters
INNER JOIN CONTACTS C ON MC.contacts = C.CONTACTS
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))
		and c.FullName = 'jacque scott'

		)

Open in new window

awking00Information Technology SpecialistCommented:
>>This query returns 3 rows.<<
>>This query returns 4 rows.<<
Can you post the data in the tables that returns those rows?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you really need only to check for the single column mc.matters to check if you need to insert a row, then you can go with the NOT IN, though it is usually inefficient with large amount of rows. Better to outer join the target table, and check for empty values:
select mc.matters
FROM ContactsQSCHEDULING CQ
INNER JOIN MattersContacts MC ON CQ.contacts = MC.Contacts 
INNER JOIN Matters M ON MC.Matters = M.Matters
INNER JOIN CONTACTS C ON mc.contacts = C.CONTACTS
left join MattersQSCHEDULING MQS on mqs.Matters = MC.Matters
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
    AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
    AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))
    and c.FullName = 'jacque scott'
    and mqs.matters is null

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
>>This query returns 3 rows.<<
 >>This query returns 4 rows.<<
 Can you post the data in the tables that returns those rows?
I agree. Telling us only the number of the rows but not providing them is useless since we can't see what's different on them.
Also, the schemas of MattersQScheduling and ContactsQScheduling can help us helping you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.