Solved

SQL Not exists

Posted on 2006-10-31
8
313 Views
Last Modified: 2012-05-05
I have

SELECT UDT02_ID, UDT09_ID
      FROM DELTEKTC.DELTEKET.ET_LINK29
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'

I then want to check that it does not exists in table below i tried the following
WHERE NOT EXISTS ( SELECT UDT09_ID, UDT02_ID FROM PCAPP.dbo.PCARD_TCL)

but errors

Incorrect syntax near the keyword 'WHERE'.

0
Comment
Question by:TRACEYMARY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17842145
Hi TRACEYMARY,


SELECT UDT02_ID, UDT09_ID
     FROM DELTEKTC.DELTEKET.ET_LINK29
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'

AND  NOT EXISTS ( SELECT 1 FROM PCAPP.dbo.PCARD_TCL)


Cheers!
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17842152
SELECT UDT02_ID, UDT09_ID
     FROM DELTEKTC.DELTEKET.ET_LINK29
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'
and NOT EXISTS ( SELECT UDT09_ID, UDT02_ID FROM PCAPP.dbo.PCARD_TCL)
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17842173
Or is this what u r looking for

SELECT t1.UDT02_ID, t1.UDT09_ID
     FROM DELTEKTC.DELTEKET.ET_LINK29 t1
WHERE UDT02_ID IS NOT NULL
AND UDT09_ID IS NOT NULL
and UDT02_ID LIKE '00401016.0004' + '%'
and NOT EXISTS ( SELECT 1 FROM PCAPP.dbo.PCARD_TCL WHERE UDT09_ID=t1.UDT02_ID AND UDT02_ID=t1.UDT09_ID )
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17843003
Whats the select 1 for ?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17843018
SELECT '1'  --will return 1 if the criteria matches, you can return any value, *, or any column name
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17843469
Thanks ......i appreciate it
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17844397
Is there a better way to write this

Write to temp table then
   loop around temp

    IF NOT exists (select x, y from table)
        Insert
 
   loop again

I understand it more when its long winded lol
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17847617
>Is there a better way to write this

This is the optimized code,
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP - How to insert data using sqlsrv on stored procedure? 8 60
Display Date and Time 7 48
Use SSRS to email customers? 4 29
SQL Query 9 28
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

734 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