How to generate SQL in relationship table without repeating values?

Question

How to generate SQL in relationship table without repeating values?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

0

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 have received videos. At the same cursor I wanted the following fields:

(Video.Url, Categoria.descricao)

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

I've been given a solution here using cursor and doing loops until I find another video, I tried to do everything in SQL, but I can not do it, does anyone know if it's possible?

I'm using id of table Video_Categoria to get the newest records.

UPDATE:

Scheme created in the SqlFiddle as requested in the comment.

sql sql-server

asked by anonymous 23.09.2015 / 15:27

3 answers

2

You can use DISTINCT to ensure that only one value is returned.

23.09.2015 / 17:15

1

Video (1, 'TestoUrl1'), (2, 'testUrl2') VideoCategory (1, 1, 1), (1, 1, 2) Category (1, 'Terror'), (2, 'Action')

If you want to return the Video URL and the Category Description, will always reordered URLs for different categories. For the return would be:

TesteUrl1, Terror  
TesteUrl1, Ação

Distinct for this case does not resolve.

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

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

Could you understand?

23.09.2015 / 18:31

Syntax error in PHP reset () command Reset button on the form

score 0 · Accepted Answer

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

Follow 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