Select records in Table 1 that are not in Table 2

[andersonjond]

I have two tables [Reviews] and [Results].

When a Review has a Status = 'Not Accepted' then there should be one or more records in [Results] that indicate what the result of the review was.

I want to structure a query to perform a data integrity check to select any Review ID that satisfies the following criteria:
- The Review Status = 'Not Accepted'
- There are zero records in [Results] for that ReviewID

I can't figure out how to do it.

Here is an example of the tables where all record are OK...

[Reviews]
ID...Status
1....Accepted
2....Not Accepted
3....Accepted
4....Not Accepted
5....Not Accepted
6....Accepted

[Results]
ReviewID....ResultID
2................5
2................6
4................3
5................3
5................7

Here is how [Results] would look if there is a data integrity problem, where the results for Review 5 are missing....

[Results]
ReviewID....ResultID
2................5
2................6
4................3

So I want to structure a query that would return the following...

ReviewID
5

Because this is a Review with Status = 'Not Accepted' but there is no corresponding record(s) in [Results].

I guess in plain English it would be like this...

SELECT ReviewID
FROM [Reviews]
WHERE (ReviewID is in [Reviews]) AND (ReviewID is NOT in [Results])

Can this be done in a single SQL query?

 

[Jon K]

Try this query (type/paste in query SQL View):-

SELECT Reviews.ID, Reviews.Status
FROM Reviews LEFT JOIN Results ON Reviews.ID = Results.ReviewID
WHERE Reviews.Status="Not Accepted" AND Results.ReviewID Is Null
.

 

[andersonjond]

Thanks, but that did not seem to work. However, I did get it to work with this idea from someone else...

SELECT Reviews.PK_ReviewID
FROM Reviews
WHERE ((Reviews.FK_StatusID)=3)
AND (((Reviews.PK_ReviewID) Not In (SELECT Results.FK_ReviewID FROM Results)));

 

[EMP]

Thanks, but that did not seem to work. However, I did get it to work with this idea from someone else...

SELECT Reviews.PK_ReviewID
FROM Reviews
WHERE ((Reviews.FK_StatusID)=3)
AND (((Reviews.PK_ReviewID) Not In (SELECT Results.FK_ReviewID FROM Results)));

The sample data given in your first post do not include a numeric field of FK_StatusID for the indications of Accepted and Not Accepted.

Besides, a Left Join with Is Null is more efficient than Not In A Subquery. If your table is fairly large, you can immediately notice the difference.

^

 

[andersonjond]

The sample data given in your first post do not include a numeric field of FK_StatusID for the indications of Accepted and Not Accepted.

^

The original posting was worded intentionally to be a simple representation of the situation and was not intended to be a technical description of the database. The intent of the post was to describe the situation simply in order to avoid side discussions about database structure, foreign keys, related lookup tables, etc. You seem to assume that all I did was type the text of the suggested solution exactly as it was posted, without any thought about whether the syntax applied correctly to the situation and might need to be modified. That would have been kinda dumb....

Besides, a Left Join with Is Null is more efficient than Not In A Subquery. If your table is fairly large, you can immediately notice the difference.

^

I appreciate your apparent attempts to be somewhat helpful, but you are once again making incorrect assumptions. This table will never have more than maybe a thousand records at most, and typically is much smaller -- generally around 350 records.

Rather than make assumptions about the situation and then add criticisms, perhaps asking questions to get clarification about why certain things were stated as they were or to get more information about the situation -- that would be less of a waste of time and would lead to a more productive discussion.

 

[EMP]

I think instead of criticizing, you should have apologized at least to Jon K for having posted misleading table structures and data and for having wasted his and other members' time.

Jon K has posted a query that works perfectly on the table structures and data given in your first post.

And only you yourself can know how many records there are in your tables. The discussions in the threads on these forums are for the benefits of anyone who comes here looking for solutions to their own problems/issues. So there is nothing wrong in pointing out which approach is the more efficient. Whether you would like to use it or not is entirely up to you.

^

 

[ajetrumpet]

Anderson,

I have talked to many people who have worked in the IT field for years, and the amount of confusion that builds up from miscommunications between IT intellectuals and non-IT users is so great that the word "frustrating" has been dropped from the IT world completely. I have certainly had MY moments of frustration with other people, but I find that the best results occur when you an extinguisher for rebuttals (if they're even warranted), rather than a torch...

 

[antoka05]

You can also use this query:
SELECT Reviews.FK_ReviewID
FROM Reviews LEFT JOIN Results
ON Reviews.FK_ReviewID = Results.FK_ReviewID
WHERE Results.FK_ReviewID is null
and reviews.FK_StatusID = 3

Regards,
Antonio

 

[Newbie100]

>>

The discussions in the threads on these forums are for the benefits of anyone who comes here looking for solutions to their own problems/issues. So there is nothing wrong in pointing out which approach is the more efficient.

<<


Well said!

Many a time my issues were solved after I did a search here.

 

[andersonjond]

I think instead of criticizing, you should have apologized at least to Jon K for having posted misleading table structures and data and for having wasted his and other members' time.

^

You have an interesting take on the world....

Your first post to this thread made no pretention of providing any help but instead simply criticized because the wording was not put in a form that YOU liked. The reply that I provided was to Jon K and HIS posting. I think he fully understood what I was trying to say, and he gave an appropriate and actually helpful response, because it got us together here to examine options we hadn't thought about and we did get something to work. Maybe we tried his approach and did it incorrectly which is why it didn't work. I don't see any angry reply from him. I don't see a reply from him stating that he feels he was misled. Do you think that he is unable to reply himself to anything that I say? Do you feel that you have to be "the great defender of the weak and helpless"? Are you so arrogant that you feel that you have to police the forum looking for posts that don't measure up to YOUR standards?

Finding a solution that works for us is the important thing, right? Or do we have to find a solution that strokes your ego? Rather than replying to a post with a criticism, you should provide helpful answers that address the questions asked (or just stay out of the conversation). You never even took an interest in trying to find out if we tried what Jon K said and did it incorectly. You just jumped right in and started bitchin'. As I stated in my initial reply to you, I worded the question in a general way intentionally to try to avoid getting critical replies from people who feel that THEIR way is the ONLY way to program anything. If you think that is "misleading" then maybe you ought to re-read the description I provided. Do you really think that the description is a technical specification of the database and the query? If you read the original description and thought that was supposed to be anything other than a generic description -- then you need to go back to school. You got lots of learnin' to do.

If you think you are so good, then why don't you publish? Perhaps it's because in a forum like this you can feel like a big fish in a little pond? Rather than wasting your time trolling the forum looking for people you can criticize, maybe you ought to do something constructive with your time. If you think your ideas are so eagerly sought after you ought to start a thread where you provide advice so people can come to you and seek answers from your depths of wisdom. That would be more helpful than providing antagonistic statements that are off topic.

If you cannot post a reasonable reply to a question and address the question asked, then it would be better to ask questions yourself to make sure your assumptions are correct first -- THEN you might have some reasonable grounds to stand on for making a criticism, and you won't risk making yourself look silly. As I said, we did figure out what we were doing wrong with Jon K's solution and we did get that query structure to work. And it works quite well. Too bad you're not interested in helping people to figure these kinds of things out.

Just to wrap it up, I chose my words purposely to avoid getting into annoying side discussions about things like this, but I guess there is no gettiing around it. Those who feel they have a responsibility to tell everyone how to live their lives just cannot be shut up.

So....

Goodbye <ah>

 

[Newbie100]

As I see it, EMP was responding to the two points he/she highlighted in bold and pointed out why it did not work:

that did not seem to work
FK_StatusID)=3


To be fair, I was one who spent quite some time trying to find a solution for you using the table structure posted by you.