Deleted items still appears in output

Question

Deleted items still appears in output

hidayah

SSC Journeyman

Points: 93
More actions
July 9, 2013 at 2:15 am

#275880

Hi All,
I'm a real newbie in SQL. My job demands me to learn it which I found out to really attractive.
So here how it goes. I have this query:
__________________________________________________________________________________________
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 LIKE 'Whole Number'
AND V2.VariableName LIKE 'Folder Name'
AND V3.VariableName LIKE 'Folder Description'
AND V4.VariableName LIKE 'Start Date'
AND V5.VariableName LIKE 'End Date'
AND V6.VariableName LIKE 'Author'
AND P.Deleted=0
_____________________________________________________________________________________________
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,
Hidayah

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

hidayah (7/9/2013)
Hi All,
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,
Hidayah

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Sean Lange (7/9/2013)
I ran your query through a formatter to make it easier to read.

You've got a SQL formatter? I'd like to hear about that!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

dwain.c (7/9/2013)
Sean Lange (7/9/2013)
I ran your query through a formatter to make it easier to read.
You've got a SQL formatter? I'd like to hear about that!

There's loads of them. Some online, some built into SSMS. I use SSMSBoost and Redgate also has a product.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

hidayah SSC Journeyman Points: 93 More actions · Answer 4

Hi,

Yes, I do get that much of rows. So how do I permanently deleted those files that I do not want?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

hidayah (7/10/2013)
Hi,
Yes, I do get that much of rows. So how do I permanently deleted those files that I do not want?

Confused...where exactly are you deleting files from? A windows folder?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 6

It appears that your company does soft deletes by setting the field "Deleted" to 1 when it should no longer be returned in queries.

Your screen shot doesn't include this column. Add it to your quest so we can see what value it holds for all records.

Mark

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

dwain.c (7/9/2013)
Sean Lange (7/9/2013)
I ran your query through a formatter to make it easier to read.
You've got a SQL formatter? I'd like to hear about that!

Here is a free one. You can use the online tool and/or download the SSMS plugin. Works pretty well.

http://poorsql.com/[/url]

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

Sean Lange (7/10/2013)
dwain.c (7/9/2013)
Sean Lange (7/9/2013)
I ran your query through a formatter to make it easier to read.
You've got a SQL formatter? I'd like to hear about that!
Here is a free one. You can use the online tool and/or download the SSMS plugin. Works pretty well.
http://poorsql.com/[/url]

Thanks Sean and Sean!

Both tools look pretty neat. Will have to try and let you guys know what I think after a test drive.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 9

Try simplifying the query for testing just to see what you have in the Projects table without all of the other INNER JOINS. (I agree with Sean that the other tables look like a dreaded EAV schema which is a PITA and a real performance killer. But that's a different topic.)

Run these two queries and it should show you the results for each state of 'Deleted' so you will have a better idea of what you are dealing with.

SELECT

P.*

FROM

Projects AS P

WHERE

P.Deleted = 0

SELECT

P.*

FROM

Projects AS P

WHERE

P.Deleted = 1

If you do in fact have rows returned with Deleted = 0 (assuming here this is a BIT datatype or at least INT) then you can run a delete command (always make a backup first!!):

DELETE FROM Projects

WHERE Deleted = 0

It's also possible that the Deleted column is a different datatype such as CHAR(1) or VARCHAR(x). That's not a good idea, but if that's what you have then perhaps the value in that column is something different than you are expecting (like Y or N) and thus you wouldn't get a match in the where clause.

hidayah SSC Journeyman Points: 93 More actions · Answer 10

Hi All,

Attached is the result that I required (the line I highlighted in blue that contains the correct info that I need).

As you can see, I have tons of rows, which includes those infos from the previously deleted files.

I need to know how to exclude those infos from those deleted files from my final output.

Actually I have use the same query for my previous project, and it works just fine. Only this time that it happened this way where the infos from deleted files still appear.

By the way, it's a Windows file.

hidayah SSC Journeyman Points: 93 More actions · Answer 11

hidayah (7/10/2013)
Hi All,
Attached is the result that I required (the line I highlighted in blue that contains the correct info that I need).
As you can see, I have tons of rows, which includes those infos from the previously deleted files.
I need to know how to exclude those infos from those deleted files from my final output.
Actually I have use the same query for my previous project, and it works just fine. Only this time that it happened this way where the infos from deleted files still appear.
By the way, it's a Windows file.

P/S: And also, the result are repetitive.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

hidayah (7/10/2013)
Hi All,
Attached is the result that I required (the line I highlighted in blue that contains the correct info that I need).
As you can see, I have tons of rows, which includes those infos from the previously deleted files.
I need to know how to exclude those infos from those deleted files from my final output.
Actually I have use the same query for my previous project, and it works just fine. Only this time that it happened this way where the infos from deleted files still appear.
By the way, it's a Windows file.

Unless you have a mechanism in place for reading file system information into SQL server and then updating your projects table, your results are not going to change. Without more information, all I can offer is an improvement on your query. This runs 10x faster in a simple test harness:

SELECT eav.*

FROM Projects AS P

OUTER APPLY (

SELECT

[Drawing Number]= MAX(CASE WHEN v.VariableName = 'Whole Number' THEN vv.ValueText END),

[Project Name]= MAX(CASE WHEN v.VariableName = 'Folder Name' THEN vv.ValueText END),

[Folder Description] = MAX(CASE WHEN v.VariableName = 'Folder Description' THEN vv.ValueText END),

[Start Date]= MAX(CASE WHEN v.VariableName = 'Start Date' THEN vv.ValueText END),

[End Date]= MAX(CASE WHEN v.VariableName = 'End Date' THEN vv.ValueText END),

[Designer]= MAX(CASE WHEN v.VariableName = 'Author' THEN vv.ValueText END)

FROM VariableValue vv

INNER JOIN Variable v ON v.VariableID = vv.VariableID

WHERE vv.ProjectID = p.ProjectID -- CORRELATION

AND v.VariableName IN ('Whole Number','Folder Name','Folder Description','Start Date','End Date','Author')

GROUP BY vv.ProjectID

) eav

WHERE P.Deleted = 0

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 13

We still need more information.

What data type is the column "Deleted"? Also, add it to your select query so we can see a sample of the data that it holds. Otherwise we have no way to help with the where clause to prevent these records from being returned.

Mark