[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How would I do a IF NOT EXISTS on this!?

Posted on 2011-04-19
2
Medium Priority
?
293 Views
Last Modified: 2012-05-11
Hey all,

How would I do a check to make sure that the row does not exists for Person_code and Subject_Code on this SQL query? Im getting unstuck with it.

This is a Job which will run every night. It is a INSERT query, but I cant seem to figure  a way out of check if those 2 columns already have those values in. It is easy enough to do if I only want to check one of the columns using a LEFT join and WHERE clause, but how about both of them?

They are not Unique rows, as there will be many Person_codes the same and many Subject_Codes the same.

 
INSERT INTO dbo.student_subject_link (person_code, subject_code)

SELECT DISTINCT
  --ncs.userid,
  pu.person_code as person_code,
  pu.unit_instance_code as subject_code
  --ncs.COURSEDESCRIPTION as course_title,
  --uis.fes_long_description as subject_title
  --ncs.coursecode 
  

FROM
  [EBS]..[FES].[NCS_NETWORK_ACCTS] ncs
    INNER JOIN [EBS]..[FES].[PEOPLE_UNITS] pu
        ON ncs.person_code = pu.person_code

    INNER JOIN [EBS]..[FES].[UNIT_INSTANCE_OCCURRENCES] uio
        ON uio.uio_id = pu.uio_id
        
    INNER JOIN [EBS]..[FES].[UNIT_INSTANCES] uis
        ON uis.fes_unit_instance_code = uio.fes_uins_instance_code
        
	LEFT JOIN dbo.student_subject_link SUBLINK
		ON pu.person_code = SUBLINK.person_code
	
WHERE

uio.fes_uins_instance_code NOT LIKE '%AP' 
AND uio.fes_uins_instance_code NOT LIKE '0%' 
AND uio.fes_uins_instance_code Not Like 'FR%' 
AND uio.fes_uins_instance_code Not Like '%W' 
AND uio.fes_uins_instance_code Not Like 'ST%' 
AND uio.fes_uins_instance_code Not Like 'ALEVL%' 
  AND uis.fes_long_description IS NOT NULL
  AND uio.offering_organisation NOT IN ('25','16','M','EX')
  AND pu.progress_status='A'
  AND pu.unit_type='R'
  AND pu.calocc_code Like dbo.caloc_code()  
  AND ncs.COURSEDESCRIPTION != uis.fes_long_description
 

--ORDER BY
  --pu.unit_instance_code
  
GO

Open in new window


0
Comment
Question by:KazooSoft
  • 2
2 Comments
 
LVL 4

Accepted Solution

by:
KazooSoft earned 0 total points
ID: 35423010
Never mind,

I fixed it using an Advanced Left Join

      LEFT JOIN dbo.student_subject_link SUBLINK
            ON pu.person_code = SUBLINK.person_code AND SUBLINK.subject_code = pu.unit_instance_code
      

0
 
LVL 4

Author Closing Comment

by:KazooSoft
ID: 35423012
I fixed the issue myself...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

873 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