• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Finding records in one column in the same table that are referring to missing records in another column

Please see attached spreadsheet.

I have two columns of data and need to creat a query to find missing records.

Document_ID and Host_Reference

Where Host_Reference IS NOT NULL, the corresponding Document_ID is denoted as an Attachment and the record listed in Host_reference is denoted as the HOST.

Where Host_Reference IS NULL, Document_ID is denoted as a Single.

I am having the problem in this dataset of identifying ALL Attachments in Document_ID that refer to HOSTS that don't exist.

ie: There is a host_reference entry, however it does not exist in Document_ID
Document_ID           Host_Reference
ABC.002.005.0094      
ABC.002.005.0116      ABC.002.005.0112
ABC.002.005.0118      

From the above example - ABC.002.005.0116 is an attachment to ABC.002.005.0112 - however ABC.002.005.0112 does not exist.

Thanks

Doug
MISSING-HOSTS.xls
0
haselwood
Asked:
haselwood
  • 5
  • 5
  • 5
  • +1
2 Solutions
 
Terry WoodsIT GuruCommented:
Try this:
select Document_ID, Host_Reference
from your_table a
where Host_Reference is not null
  and not exists
    (select * from your_table b
       where b.Document_ID = a.Document_ID 
         and b.Host_Reference is null
    )

Open in new window

0
 
Terry WoodsIT GuruCommented:
Can a document with an attachment *be* an attachment, such as the 0094 document below? If so, you'll need a slightly different query.
Document_ID           Host_Reference
ABC.002.005.0092      ABC.002.005.0094      
ABC.002.005.0116      ABC.002.005.0112
ABC.002.005.0094      ABC.002.005.0118
ABC.002.005.0118
0
 
Kevin CrossChief Technology OfficerCommented:
Or this:
select Document_ID, Host_Reference
from your_table a
where Host_Reference NOT IN
(select Document_ID from your_table)

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
haselwoodAuthor Commented:
Thanks for the query - however no - a HOST can never be an attachment.

Therefore, if a document exists in HOST_REFERENCE, then it is a HOST.

Doug.
0
 
Kevin CrossChief Technology OfficerCommented:
My query should still work, but to be more precise, you can add this to the where clause query to ensure only looking at host records.
select Document_ID, Host_Reference
from your_table
where Host_Reference NOT IN
(select NULL As Host UNION ALL select Document_ID from your_table where host_reference IS NULL)

Open in new window

0
 
Terry WoodsIT GuruCommented:
My first suggestion should work too, for the record :-)
0
 
haselwoodAuthor Commented:
mwvisa1 - your query above didn't work for me...

the error recieved mentions "This operation is not allowed in subqueries"

Ideas?

Thanks

Doug.
0
 
Terry WoodsIT GuruCommented:
Have you tried my suggestion?
0
 
Kevin CrossChief Technology OfficerCommented:
Try this then -- It is like what Terry started you out with.

@TerryAtOpus, I was responding to the asker and not indicating anything about your query. ;)
select Document_ID, Host_Reference
from your_table a
where Host_Reference IS NOT NULL 
AND Host_ReferenceNOT IN (select Document_ID from your_table where host_reference IS NULL)

Open in new window

0
 
haselwoodAuthor Commented:
I did - and it was incorrect -

An example of what is was returning was saying that

ABC.002.005.0061      
ABC.002.005.0062      ABC.002.005.0061
ABC.002.005.0063      


ABC.002.005.0061 didn't exist in Document_ID - however it does.

I've found a few other examples as well.

Thoughts?

Thanks for the help - it is very much appreciated

0
 
Kevin CrossChief Technology OfficerCommented:
This should pull all records where you have a host in host_reference and then check that the host_reference is not in the listing of document_id's that do not have a host_reference defined which should be listing of hosts if I understood you correctly.  I tried to shorthand with the union in the IN clause and that may have been why you got an error. :)

I reposted this query as my cut and paste originally left the NOT improperly spaced from host_reference.

Regards,
Kevin
select Document_ID, Host_Reference
from your_table a
where Host_Reference IS NOT NULL 
AND Host_Reference NOT IN (select Document_ID from your_table where host_reference IS NULL)

Open in new window

0
 
Terry WoodsIT GuruCommented:
I just tried some of these queries in a mock-up database, and mwvisa1's latest suggestion seems to work...
0
 
haselwoodAuthor Commented:
Thanks so much for your help guys - much appreciated.
0
 
GRayLCommented:
The dirty word here is 'spreadsheet'
0
 
haselwoodAuthor Commented:
I...don't get it.
0
 
GRayLCommented:
Neither do I.  In your example  ABC.002.005.0112 exists, yet you say it doesn't.  Must be the spreadsheet!
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.  

GRayL, take a look again and you will see that per the description of the query the ABC.002.005.0112  host doesn't exist as a host record which is one in which that value is a document_id with a null host_reference.  The Q is to find attachment references to hosts that don't exist under my previous definition.

Anyway, glad we could help.

Regards,
Kevin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now