Duplicate Rows

[murphyhg]

Any thoughts on how I can clean this up a little and not have duplicate rows?

SELECT
DISTINCT Articles.Title AS Title, Articles.ByPassURL, Articles.LocName AS ParentName,
Articles.description, ARTICLEMATRIX.TIER_ID, ARTICLEMATRIX.TIER,
(select art_name from articletype where articletype.id = ARTICLEMATRIX.ART_TYPES) AS ART_TYPES,
(select url from dbo.getContentURL3(Articles.ID, Articles.ID, Articles.ByPassURL)) AS URL,
(select url from dbo.getContentURL2(Articles.ID, Articles.ID)) AS ID,
ARTICLEMATRIX.ART_ID
FROM Articles INNER JOIN
ARTICLEMATRIX ON Articles.ID = ARTICLEMATRIX.ART_ID
WHERE (Left(Ltrim(Articles.ByPassURL),4) = 'http' OR (Articles.ByPassURL IS NULL) OR Right(RTrim(Articles.ByPassURL), 3) = 'pdf' OR Right(RTrim(Articles.ByPassURL), 3) = 'doc')
AND (ARTICLEMATRIX.TIER IN (3) ) AND (ARTICLEMATRIX.TIER_ID IN (select t3id from tier3 where t2idfk = 5))
AND (Articles.PUBDATE <= { fn NOW() }) AND (Articles.EXPDATE >= { fn NOW() }) AND (Articles.artstatus = 1)
AND (ARTICLEMATRIX.ART_TYPES IN (1,2,4,5,6))
ORDER BY ART_TYPES

 

[r937]

not sure what "clean this up a little" might mean other than some sensible indenting and removing unnecessary parentheses...

SELECT DISTINCT 
       Articles.Title
     , Articles.ByPassURL
     , Articles.LocName AS ParentName
     , Articles.description
     , ARTICLEMATRIX.TIER_ID
     , ARTICLEMATRIX.TIER
     , ( SELECT art_name 
           FROM articletype 
          WHERE articletype.id = ARTICLEMATRIX.ART_TYPES ) AS ART_TYPES
     , ( SELECT url 
           FROM dbo.getContentURL3( Articles.ID
                                  , Articles.ID
                                  , Articles.ByPassURL) ) AS URL
     , ( SELECT url 
           FROM dbo.getContentURL2( Articles.ID
                                  , Articles.ID) ) AS ID
     , ARTICLEMATRIX.ART_ID  
  FROM Articles 
INNER 
  JOIN ARTICLEMATRIX 
    ON ARTICLEMATRIX.ART_ID = Articles.ID 
 WHERE (
       LEFT(LTRIM(COALESCE(Articles.ByPassURL,'http'),4) = 'http' 
    OR RIGHT(RTRIM(Articles.ByPassURL),3) IN ( 'pdf','doc' )
       )  
   AND ARTICLEMATRIX.TIER = 3
   AND ARTICLEMATRIX.TIER_ID IN 
           ( SELECT t3id 
               FROM tier3 
              WHERE t2idfk = 5)
   AND Articles.PUBDATE <= { fn NOW() }  
   AND Articles.EXPDATE >= { fn NOW() }  
   AND Articles.artstatus = 1 
   AND ARTICLEMATRIX.ART_TYPES IN (1,2,4,5,6) 
ORDER 
    BY ART_TYPES

as for duplicate rows, you cannot possibly be getting duplicate rows, because you're using DISTINCT

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[murphyhg]

Thanks for doing this. I have a question what does the Coalesce Function do for me?

 

[markros]

The simplest way to find an answer about COALESCE() function would be to check BOL (books online)

http://msdn.microsoft.com/en-us/library/ms190349.aspx

See also

http://www.builderau.com.au/program...CE-in-SQL-Server/0,339028455,339284811,00.htm

PluralSight Learning Library