Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deleted items still appears in output Expand / Collapse
Author
Message
Posted Tuesday, July 09, 2013 2:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 5:06 AM
Points: 5, 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


  Post Attachments 
Untitled.jpg (15 views, 139.10 KB)
Post #1471473
Posted Tuesday, July 09, 2013 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 11,977, Visits: 10,998
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 Moden's 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)
Post #1471652
Posted Tuesday, July 09, 2013 7:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1471914
Posted Wednesday, July 10, 2013 3:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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
Post #1472026
Posted Wednesday, July 10, 2013 3:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 5:06 AM
Points: 5, Visits: 19
Hi,

Yes, I do get that much of rows. So how do I permanently deleted those files that I do not want?
Post #1472032
Posted Wednesday, July 10, 2013 5:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1472051
Posted Wednesday, July 10, 2013 5:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
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



Post #1472052
Posted Wednesday, July 10, 2013 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 11,977, Visits: 10,998
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 Moden's 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)
Post #1472114
Posted Wednesday, July 10, 2013 6:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1472406
Posted Wednesday, July 10, 2013 7:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1472412
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse