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.