Click here to Skip to main content
15,117,095 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Why this query is not giving a result with where clause. If I don't use where clause then I get some results but that does not use full for me I want to use where clause.
Can anyone help me, please?



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

where Documents.Complete = 0

What I have tried:

Sql server inner joins with where clause
Posted
Updated 13-Nov-17 7:00am

you can try something like
SELECT  
       Complete
       ,COUNT(0) AS cnt
FROM  Documents
GROUP BY
       Complete
ORDER BY 2 DESC	 

this will give you an idea of the values that might make sense as a where clause.

Taking your comment below into account, you can add

CASE WHEN Documents.Complete = 0 THEN 'pending'
     WHEN Documents.Complete = 1 THEN 'has completed'
     ELSE 'something strange happend'
END AS DocumentStatus


to your column list (and don't use the where clause). But from what you wrote I'm not sure what your desired output should look like.
   
v2
Comments
Member 10754595 13-Nov-17 11:51am
   
I am trying to create a report of who have completed and who have not with the help of View so that I can run it everytime
If adding the WHERE clause returns no rows, then it's because there are no JOINed rows that match the criteria. There isn't anything we can do to fix that: it's your data, and we don't have access to that.
Add dbo.Documents.Complete to your SELECT list, and remove the WHERE clause.
Run the query, and you will see that none of the JOINed rows that the WHERE would be working on would match - to get the results you want, you will have to look at the rest of your data and isolate why that is the case, and we can't do that!
   
Comments
Member 10754595 13-Nov-17 12:06pm
   
If I do where Documents.Complete = 1
it is giving a result only not working with 0. And in my table,there are incomplete documents
OriginalGriff 13-Nov-17 12:15pm
   
So what do you get when you do what I suggested?
Remember, you need to analyse a problem before you can fix it - and that means gathering information first.
Member 10754595 13-Nov-17 12:21pm
   
yes I did not result with 0
OriginalGriff 13-Nov-17 12:35pm
   
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!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900