How to get incomplete data from where clause

Question

0.00/5 (No votes)

See more:

I am trying to create a report depends upon a signature table.Here is my query
If the document is complete then this query is giving complete document result based on the signature.But when I want to get incomplete document result this query is not giving me result and without using where clause I don't know how I can get complete = 0

SELECT Employees.FirstName, Employees.LastName, Signatures.Employee_ID, Signatures.Document_ID, Documents.Topic, Documents.Complete, Documents.Created,
Documents.RevisionLevel
FROM Employees INNER JOIN
Signatures ON Signatures.Employee_ID = Employees.ID INNER JOIN
Documents ON Signatures.Document_ID = Documents.ID

where Documents.Complete = 0

What I have tried:

I tried SQL Joins to get the desired result

Posted 16-Nov-17 7:31am

Member 10754595

Updated 16-Nov-17 8:04am

v2

Add a Solution

Comments

ZurdoDev 16-Nov-17 14:03pm

What do you want from us? We cannot see your data or your table structures so how can we possibly help you?

Member 10754595 16-Nov-17 14:09pm

just want to know why the same query is working for complete but not for incomplete.
there is a report which is fetching data from the document and employee table employeeID and document id only will appear in the signature table if someone has sigh off from that document. I don't know how I can show my table structure to you

ZurdoDev 16-Nov-17 14:12pm

Again, we don't know what complete and incomplete mean in your data.

"and document id only will appear in the signature table if someone has sigh off from that document." There is your problem. INNER JOINing to the Signatures table means it must have a signature. If you LEFT JOIN instead then you'll still get records but any columns from that table will be NULL if there is no match.

RedDk 16-Nov-17 14:35pm

Just jumping to a conclusion here, but are you really saying that there's an abbreviation of data that's taking place somewhere and now you're dealing with the long and the short of it? In which case perhaps you need to DECLARE string types using larger containers?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-11-16T08:01:00

When you asked this same question three days ago: Using where clause with inner joins[^] I told you what to do:

Quote:
So, that means that the WHERE is not your problem: it's the JOINs that are removing them.
Manually find a row that should be in your output - that has a zero - and look at the JOINed fields - why isn't it included? Until you know that, you can't fix this: and I have no access to your data at all!

The fact that we cannot access your data to do that for you has not changed in three days; nor will it in another three!