How to generate SQL in relationship table without repeating values?

Question

I have the following scenario: Tables:

Video (id, url)
VideoCategoria (id, id_video, id_categoria)
Categoria (id, descricao)

I need to get the last 120 categories that received videos. On the same cursor I wanted the following fields:

(Video.Url, Categoria.descricao)

Only you can’t repeat the url video, for example, if a single video enters two different categories then category 1 and 2 are coming with the same Url.

A solution has already been given here using cursor and making loops until finding another video, I tried to do everything in SQL, but I’m not able to do it, someone knows if it is possible?

I am using the id table Video_Categoria to pick up the newest records.

UPDATING:

Scheme created on Sqlfiddle as requested in the comment.

Answer 1

you can use the DISTINCT to ensure that only one value will be returned.

Answer 2

Video (1, 'Testourl1'), (2, 'testeUrl2') Videocategoria(1, 1, 1), (1, 1, 2) Category(1, 'Terror'), (2, 'Action'')

If you want to return the Video URL and Category Description, will always go return URL to different categories. Because the return would be:

TesteUrl1, Terror  
TesteUrl1, Ação

Distinct for this case does not solve.

The hint I give is to use the URL as the name of the return field and the data is the description, being this way for example:

TesteUrl1    TesteUrl2
Terror       ação
Ação         Terror

I could tell?

Answer 3

I was able to build with the help of Entity Framework and the rest I finished myself.

Follows the solution:

select 
	v.url,
	c.id,
	c.description
	
	
	 from (
select top 120 a.id_video, max(id_categoria) as id_categoria from (


SELECT  
    [Join2].[ID] AS [ID], 
    [Join2].[ID_VIDEO] AS [ID_VIDEO], 
    [Join2].[ID_CATEGORIA] AS [ID_CATEGORIA]
    FROM   (SELECT 
        [Extent2].[ID_VIDEO] AS [K1], 
        MAX([Extent2].[ID_VIDEO]) AS [A1]
        FROM   (SELECT 
            [Extent1].[ID_CATEGORIA] AS [K1], 
            MAX([Extent1].[ID]) AS [A1]
            FROM [VIDEO_CATEGORIA] AS [Extent1]
            GROUP BY [Extent1].[ID_CATEGORIA] ) AS [GroupBy1]
        INNER JOIN [VIDEO_CATEGORIA] AS [Extent2] ON ([GroupBy1].[K1] = [Extent2].[ID_CATEGORIA]) AND ([Extent2].[ID] = [GroupBy1].[A1])
        GROUP BY [Extent2].[ID_VIDEO] ) AS [GroupBy2]
    INNER JOIN  (SELECT [Extent4].[ID] AS [ID], [Extent4].[ID_VIDEO] AS [ID_VIDEO], [Extent4].[ID_CATEGORIA] AS [ID_CATEGORIA]
        FROM   (SELECT 
            [Extent3].[ID_CATEGORIA] AS [K1], 
            MAX([Extent3].[ID]) AS [A1]
            FROM [VIDEO_CATEGORIA] AS [Extent3]
            GROUP BY [Extent3].[ID_CATEGORIA] ) AS [GroupBy3]
        INNER JOIN [VIDEO_CATEGORIA] AS [Extent4] ON ([GroupBy3].[K1] = [Extent4].[ID_CATEGORIA]) AND ([Extent4].[ID] = [GroupBy3].[A1]) ) AS [Join2] ON ([GroupBy2].[K1] = [Join2].[ID_VIDEO]) AND ([Join2].[ID_VIDEO] = [GroupBy2].[A1])

		) as a


group by a.id_video
order by id_video
) as grupo

join 
[CATEGORIA] as c
on grupo.id_categoria = c.id
join 
[VIDEO] as v
on grupo.id_video = v.id