Link to home
Create AccountLog in
Avatar of jbrahy
jbrahy

asked on

how do I get distinct results from a union?

I am working on a search query that queries a table twice, once for each column that I'm doing a containstable clause against. I'm getting duplicate results because the search critera is matching both columns. Is there a way to either search two columns or get a distinct list from this query?

        SELECT  FT_Media.MediaID,
      FT_Media.EventID,
                FT_Media.Title,
                FT_Media.Caption,
                'Image' AS SearchResultType,
                ( KEY_Media.RANK / @NameDivisor ) AS RankValue
        FROM    dbo.Media FT_Media
                INNER JOIN CONTAINSTABLE(dbo.Media, ( Title ), @ContainsClause)
                AS KEY_Media ON FT_Media.MediaID = KEY_Media.[KEY]
        WHERE   FT_Media.EventID IN ( SELECT DISTINCT
                                                Events.EventID
                                      FROM      dbo.Events
                                      WHERE     Events.StatusID = @statusID )
                AND FT_Media.MediaTypeID = @MEDIA_TYPE_VIDEO
        UNION
        SELECT  FT_Media.MediaID,
      FT_Media.EventID,
                FT_Media.Title,
                FT_Media.Caption,
                'Image' AS SearchResultType,
                ( KEY_Media.RANK / @DescriptionDivisor ) AS RankValue
        FROM    dbo.Media FT_Media
                INNER JOIN CONTAINSTABLE(dbo.Media, ( Caption ), @ContainsClause)
                AS KEY_Media ON FT_Media.MediaID = KEY_Media.[KEY]
        WHERE   FT_Media.EventID IN ( SELECT DISTINCT
                                                Events.EventID
                                      FROM      dbo.Events
                                      WHERE     Events.StatusID = @statusID )
                AND FT_Media.MediaTypeID = @MEDIA_TYPE_VIDEO
        ORDER BY RankValue DESC



I just posted this and someone came up with a solution of putting
        SELECT DISTINCT
                ImageUnion.MediaID,
                ImageUnion.EventID,
                ImageUnion.Title,
                ImageUnion.Caption,
                ImageUnion.SearchResultType,
                ImageUnion.RankValue
        FROM    ( original query here )
        ORDER BY RankValue DESC


but the rank value is different between the two queries. Is there another way to do it?

Avatar of chapmandew
chapmandew
Flag of United States of America image

post some of the data that is returned...

BTW...UNION only returns distinct data, UNION ALL returns duplicates.  There HAS to be a field in your dataset that differentiates two records.
Try below:

SELECT DISTINCT * FROM
(
        SELECT  FT_Media.MediaID,
      FT_Media.EventID,
                FT_Media.Title,
                FT_Media.Caption,
                'Image' AS SearchResultType,
                ( KEY_Media.RANK / @NameDivisor ) AS RankValue
        FROM    dbo.Media FT_Media
                INNER JOIN CONTAINSTABLE(dbo.Media, ( Title ), @ContainsClause)
                AS KEY_Media ON FT_Media.MediaID = KEY_Media.[KEY]
        WHERE   FT_Media.EventID IN ( SELECT DISTINCT
                                                Events.EventID
                                      FROM      dbo.Events
                                      WHERE     Events.StatusID = @statusID )
                AND FT_Media.MediaTypeID = @MEDIA_TYPE_VIDEO
        UNION
        SELECT  FT_Media.MediaID,
      FT_Media.EventID,
                FT_Media.Title,
                FT_Media.Caption,
                'Image' AS SearchResultType,
                ( KEY_Media.RANK / @DescriptionDivisor ) AS RankValue
        FROM    dbo.Media FT_Media
                INNER JOIN CONTAINSTABLE(dbo.Media, ( Caption ), @ContainsClause)
                AS KEY_Media ON FT_Media.MediaID = KEY_Media.[KEY]
        WHERE   FT_Media.EventID IN ( SELECT DISTINCT
                                                Events.EventID
                                      FROM      dbo.Events
                                      WHERE     Events.StatusID = @statusID )
                AND FT_Media.MediaTypeID = @MEDIA_TYPE_VIDEO
        ORDER BY RankValue DESC
) as tempTable
Avatar of jbrahy
jbrahy

ASKER

the problem is that the rankvalue is different in each of the queries in the union so distinct is working because the rows are different. I need to get one of the two rows, not both.
You using 2005?
Avatar of jbrahy

ASKER

yes
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jbrahy

ASKER

awesome, thank you very much