I'm a real newbie in SQL. My job demands me to learn it which I found out to really attractive.
I was told that, having the last line(AND P.Deleted=0) in my above query would help me to exclude the files that I have deleted in the folder where I apply this query in.
But the problem now is, those deleted files still appear. Well, it happened the same way when I put =1. You can see from the attachment. I have tons of outputs, whereas in this case I just need only one because only one file left in the folder I'm working with now. FYI, I'm working with SolidWorks Enterprise Product Data Management where I need to create a report generator for my client.
I was hoping that if anybody can help me on this since my dateline is getting near. If more info is needed to solve this, do let me know.
Thanks & Best Regards,
You have us at an extreme disadvantage here. We don't know your system and have no idea what your tables look like. I don't understand how changing p.Deleted from 0 to 1 returns the same information.
The query you posted makes me think this entire system is an EAV (entity attribute value), which is VERY painful to work with.
I ran your query through a formatter to make it easier to read.
SELECT VV1.ValueText AS [Drawing Number],
VV2.ValueText AS [Project Name],
VV3.ValueText AS [Folder Description],
VV4.ValueText AS [Start Date],
VV5.ValueText AS [End Date],
VV6.ValueText AS [Designer]
FROM Projects AS P
INNER JOIN VariableValue AS VV1 ON P.ProjectID = VV1.ProjectID
INNER JOIN Variable AS V1 ON VV1.VariableID = V1.VariableID
INNER JOIN VariableValue AS VV2 ON P.ProjectID = VV2.ProjectID
INNER JOIN Variable AS V2 ON VV2.VariableID = V2.VariableID
INNER JOIN VariableValue AS VV3 ON P.ProjectID = VV3.ProjectID
INNER JOIN Variable AS V3 ON VV3.VariableID = V3.VariableID
INNER JOIN VariableValue AS VV4 ON P.ProjectID = VV4.ProjectID
INNER JOIN Variable AS V4 ON VV4.VariableID = V4.VariableID
INNER JOIN VariableValue AS VV5 ON P.ProjectID = VV5.ProjectID
INNER JOIN Variable AS V5 ON VV5.VariableID = V5.VariableID
INNER JOIN VariableValue AS VV6 ON P.ProjectID = VV6.ProjectID
INNER JOIN Variable AS V6 ON VV6.VariableID = V6.VariableID
WHERE V1.VariableName = 'Whole Number'
AND V2.VariableName = 'Folder Name'
AND V3.VariableName = 'Folder Description'
AND V4.VariableName = 'Start Date'
AND V5.VariableName = 'End Date'
AND V6.VariableName = 'Author'
AND P.Deleted = 0
Do you get the same 76800 rows when p.Deleted = 1?
Keep in mind that the existence of that columns indicates that it is not actually deleted. This is called a soft delete. In other words, the data still exists it is only logically deleted.
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)