Solved

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

Posted on 2008-09-30
17
235 Views
Last Modified: 2011-10-19
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
Comment
Question by:haselwood
  • 5
  • 5
  • 5
  • +1
17 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22610739
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22610755
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22610761
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:haselwood
ID: 22610765
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22610790
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22610797
My first suggestion should work too, for the record :-)
0
 

Author Comment

by:haselwood
ID: 22610943
mwvisa1 - your query above didn't work for me...

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

Ideas?

Thanks

Doug.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22610951
Have you tried my suggestion?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22610959
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
 

Author Comment

by:haselwood
ID: 22610968
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 350 total points
ID: 22610986
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
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 150 total points
ID: 22611008
I just tried some of these queries in a mock-up database, and mwvisa1's latest suggestion seems to work...
0
 

Author Closing Comment

by:haselwood
ID: 31501811
Thanks so much for your help guys - much appreciated.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22611038
The dirty word here is 'spreadsheet'
0
 

Author Comment

by:haselwood
ID: 22611043
I...don't get it.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22611088
Neither do I.  In your example  ABC.002.005.0112 exists, yet you say it doesn't.  Must be the spreadsheet!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22611129
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 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