SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleted items still appears in output


Deleted items still appears in output

Author
Message
hidayah
hidayah
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 19
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
Attachments
Untitled.jpg (21 views, 139.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64159 Visits: 17974
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.

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)
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18263 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4170 Visits: 3436
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

@SeanPearceSQL

About Me
hidayah
hidayah
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 19
Hi,

Yes, I do get that much of rows. So how do I permanently deleted those files that I do not want?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42460 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
Mark Eckeard
Mark Eckeard
Say Hey Kid
Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)

Group: General Forum Members
Points: 669 Visits: 505
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
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64159 Visits: 17974
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/

_______________________________________________________________

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)
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18263 Visits: 6431
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/


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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2097 Visits: 1721
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.

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search