-1

pretty new to sql in general, I have to identify 52 "empty" entries in our db to see which contacts they are connected to, if any, and have been unable to isolate them so far. We are using mssql 7.0 on a windows 2000 vm.

What i mean by "empty" entries is that when a user opens the db application to search for contacts, which are listed in alphabetical order, the first 52 contacts are simply all blank. You can scroll through them one by one, but there is no information attached to them.

I have tried simple select statements, but have been unsuccesful in isolating the entries.

Here are a few examples of the select statements I've tried:

Ex.1

SELECT DISTINCT Kon_NachN, Kon_VorN, Kon_ID
FROM tbl_Kontakte
WHERE Kon_Fa='' AND Kon_NachN ='' AND Kon_VorN =''

Delivers 159 rows

Ex.2

SELECT DISTINCT Kon_ID, Kon_Fa, Kon_VorN, Kon_NachN
FROM tbl_Kontakte
WHERE Kon_Fa is NULL AND Kon_VorN is NULL AND Kon_NachN is NULL
ORDER BY Kon_ID ASC

Delivers 0 rows

Ex.3

I noticed when i selected all Kon_ID that certain numbers were missing and tried to isolate those, but was unsuccessful with the 2 statements below

SELECT * 
FROM tbl_Kontakte
ORDER BY Kon_ID ASC

Missing Kon_ID are for example 3, 12, 17, 33,87,88,96,97

SELECT *
FROM tbl_Kontakte
WHERE Kon_ID ='3' OR Kon_ID ='12' OR Kon_ID ='17'
ORDER BY Kon_ID ASC

Delivers 0 rows

These entries are definitely not there, but i couldnt figure out how to find any attached info, but i figured with 8 missing Kon_IDs in the first 100 entries in a db with over 4000 entries that i must be off the mark.

Ex.4

SELECT *
FROM tbl_Kontakte
WHERE (Kon_VorN is null or Kon_VorN='') and (Kon_NachN is null or Kon_NachN='')
ORDER BY Kon_ID ASC

Delivers 3359 rows

Since this is pretty much my first crack at SQL outside of online tutorials (also first time posting here), i guess i figured i would find 52 results matching the number of "empty" entries and be able to go from there fixing each entry to show the appropriate information. Sorry if this is all unclear or has an obvious solution, but i thought i would see if anyone had any ideas.Thanks.

mello
  • 1
  • 1
  • 2
    That screen shot is clearly not SQL Server and SQL Server 7.0 was released some 21 years ago, so I doubt you're using that. If you are, you have some significant problems (support for SQL server 7 finished in 2011!). What RDBMS are you *really* using? – Thom A Apr 05 '19 at 10:00
  • 1
    Well they did say it's on a Windows 2000 VM, and 2000 is not supported since July 2010, so it's entirely possible they are indeed using SQL Server 7. However terrifying, it might just be true. – MarcinJ Apr 05 '19 at 10:18
  • Oh wow, i didn't notice that they stated they're using Windows 2000. Yeah, that is a terrifying prospect @MarcinJ . – Thom A Apr 05 '19 at 10:21
  • I've added the tags, as you are likely correct, they really *are* using SQL Server 7.0, @MarcinJ. *shivers*. At the OP, I think you need to explain why you're using such old technology. Windows 2000 ended support in 2010, and SQL Server 7.0 in 2011. using something that old are severe security concerns. – Thom A Apr 05 '19 at 10:24
  • Looking at your screen, the list seems to be ordered by the value labelled "Firma". There should be a column in your table with that same name, so try – SMor Apr 05 '19 at 12:58
  • Alternatively select the top 100 rows and order by Firma. Eg. – SMor Apr 05 '19 at 12:59

1 Answers1

0

Your application, where the screenshot comes from, probably orders by "something" (or further filters the results it's displaying) and it just seems there are 52 empty records to you, but in fact, you have 3359 of them with empty Kon_VorN and Kon_NachN of which 159 also have empty Kon_Fa (based on Ex1 query). Probably if you scrolled through all the records in the app, you'd find more of them.

These missing entries (Kon_ID = 3, 12, ...) have probably been deleted from the database at some point, so that's why you're missing them.

Now if you want to find records with all fields empty, you just have to include all the fields in the WHERE clause. It's quite impossible to find a function reference for SQL Server 7.0 at this point, but I don't think either ISNULL, NULLIF or COALESCE are available to help you with simplifying the check for either NULL or empty string.

MarcinJ
  • 3,471
  • 2
  • 14
  • 18