| Author |
Topic |
|
SQLJames
Starting Member
35 Posts |
 Posted - 2010-01-05 : 11:59:18
|
| I searched, got impatient, and couldn't find anything in my limited attention span, so I pre-apologize if this has been answered or posted somewhere else.I have a table: PACKAGEColumn: Package ID varchar(10)Primary Key: Package IDSample data (CSV): '123456789', '987654321', '456789123'I want to write a query that can tell me if a record does not exist.For the above three records, they exist. What I want to do is be able to write a query to tell me that record '999999999' does not exist in the table. Is that possible? Thank you in advance for your consideration and assistance! |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-05 : 12:21:32
|
| It is, but you will have to write an awfully long table to cross apply it to. Out of 1 Million records how many are you expecting dont exist? Also, are the ones that do exist in sequential order? like if 123456789 exists, does every ID before exist as well?Or do you have a table of all possible IDs already located someplace? Something you could left join to. |
 |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2010-01-05 : 12:42:19
|
| You can always use something simple to create this IF EXISTS (SELECT * FROM PACKAGE WHERE PackageID = '999999999')PRINT 'Exists'ELSEPRINT 'Does Not Exists'-----------------------------------------------Learning something new on SQL Server everyday. |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-05 : 12:47:12
|
| Ah sorry I was not thinking this as a 1 off type deal, I thought he wanted to scroll through all of the IDs and show which did not exist, sorry if I had the wrong idea. |
|
|
|
SQLJames
Starting Member
35 Posts |
Posted - 2010-01-05 : 15:04:20
|
| First, thank you NeilG, and DP978 for your replies and assistance.To answer your questions, I don't have a table with the values in it that I can check against - so no left joins or not exists. That is why I am in the pickle I am in.The solution that you gave NeilG is a move in a direction that I will have to go. It will work but as with everything it will take some tweaking because the number of packages I get in the query is variable and that complicates matters. But your suggestion helps me in the direction that I need to go.Thank you both for your wise and prompt advice.James |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2010-01-05 : 15:17:33
|
| You have to use a variable instead of '999999999' in the above query. Or see whether you can create a Scalar valued function that would get the package id and return a BIT value 'yes' or 'no' to determine the record existance.. |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-05 : 15:21:38
|
| Realistically you could create the table once if you are going to do this a lot and then run off of the table with a left join whenever you needed. It's a lot of numbered rows for nothing but if space isnt an issue it might save time rather than generating a query that loops through each run. |
|
|
|
SQLJames
Starting Member
35 Posts |
Posted - 2010-01-06 : 09:18:37
|
| Again, thank you for the suggestions and comments.I believe that I found a solution using a mixture of the suggestions. Also, I found a table that HAS all the existing packages already so I can query against that (one of the benefits of large systems is that you may eventually find what you need to do your job - if the system was designed with forethought).Thank you for all of your excellent advice and comments.James |
|
|
|
|
Record does not exists