How to stop data redundancy

Hey guys...i've got a problem wif my stored procedures...in which my page keep repeating the same data...so to counter this problem i use SELECT DISTINCT instead of just SELECT..but the problem is when i change SELECT into SELECT DISTINCT...the page will not be display,page error...for your information my stored procures was auto generate from some security sofware...so can guys help me out with my code...

SELECT DISTINCT t.TargetID,
'V' RecordType,
t.TargetDNSName [Target DNS Name],
t.TargetIPAddress [Target IP],
t.TargetIPDisplay [Target IP Display],
t.TargetOSName,
t.TargetOSRevisionLevel,
v.SecChkID,
v.Severity,
sc.TagName [Tag Name],
sc.ChkBriefDesc [Tag Brief Desc],
sc.ChkName [Tag Chk Name],
CONVERT(NVARCHAR(4000),sc.ChkDetailDesc) [Tag Detail Desc],
CONVERT(NVARCHAR(4000),r.RemedyDesc) [Remedy],
o.ObjectID,
o.ObjectTypeDesc [Object Type Desc],
o.ObjectName [Object Name],
s.SensorDataID,
a.AttributeName,
a.AttributeValue,
NULL [Port],NULL [Service Name],NULL [Protocol]

FROM #Vulns v
INNER JOIN TargetHost t (NOLOCK)
ON v.TargetID = t.TargetID
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
INNER JOIN ObjectView o (NOLOCK)
ON v.ObjectID = o.ObjectID
LEFT OUTER JOIN SensorData1 s WITH (NOLOCK, INDEX(SensorData1_AK3))
ON v.ObservanceID = s.ObservanceID
AND s.Cleared = 'n'
LEFT OUTER JOIN SensorDataAVP a (NOLOCK)
ON s.SensorDataID = a.SensorDataID
AND a.AttributeValue IS NOT NULL
AND a.AttributeValue != ''
UNION ALL

In the FROM clause, please notice that there is an INNER JOIN statement that has no ON condition but was directly followed a LEFT OUTER JOIN as specified on this line:
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)

I don't know if you have intended to do this, but this statement looks weird. Please try to review this clause.

|||

Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID

|||

selipeh wrote:

Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID

That is not the correct syntax for SQL Server as far as I amaware. You say you are getting an error. Please provide the errortext.|||thanks tmorton,actually there is no error...the code was fine...the only problem is when it comes to display process...it won't display anything just "page cannot be display"...but when i delete the DISTINCT it will display my .aspx page with a lot of data redundancy....|||What happens when you run your stored procedure through Query Analyzer?|||

i check in sql analyzer...but there was no error...just simply it won't display anythings,but when i delete DISTINCT...it will be fully functioning with data redundancy

|||Well, that makes no sense. The removal of the DISTINCT keyword inand of itself would not cause a query to stop returning results. My guess is that more changed than the removal of the DISTINCT keyword.|||

As far as I am aware the query isn't correct either.

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
should be

INNER JOIN(SELECT * FROMSecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID) somename
ON v.SecChkID = sc.SecChkID

|||On top of that, I'm not sure what the UNION ALL is supposed to be doing at the end of the query.|||The UNION ALL operator is used in old style ANSI SQL JOINs and that is the reason for the duplicates because the UNION operator performs an implict distinct by removing duplicates but UNION ALL leaves duplicates in. I think the UNION ALL operator could be changed to one or two AND operators. Hope this helps.|||

I know what UNION ALL does, but it isn't syntatically correct as the last thing in a query. UNION ALL *MUST* be followed by a second query in which case it does caddre said. It combines the output of both queries and doesn't remove the duplicates.

Toss this in Query Analyzer:

SELECT '1'
UNION ALL

You get a syntax error.

|||You get syntax error because there is no UNION in you query but the poster's UNION ALL is at the end of 5 JOIN operations and 3 AND operators so SQL Server maybe ignoring some of the JOINS because the CONVERT sometimes makes tables UNION compatible and running UNION ALL instead. I am not saying the query cannot be modified but it works now so replace the UNION ALL with AND operators to start.|||

3rd syntax error:

INNER JOIN TargetHost t (NOLOCK)

should be

INNER JOIN TargetHost t WITH (NOLOCK)

Infact that's done a lot in the query, and they are all wrong. The query as posted won't run period, and should toss up an error. If it is not, it's only because with so many syntax errors you have thoroughly confused the T-SQL parser.

|||The poster said the code runs, you are running DML(data manipulation language) without DDL(data definition language). That is the reason SQL forums always ask for DDLs so when the person post some DDL you can run it in RedGate or BMC. I have TOAD here because I run Oracle.