?
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
Medium Priority
?
241 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
[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
  • 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 60

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
Technology Partners: 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!

 

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 60

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 1400 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 600 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 60

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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