discriminatory query sql server

Question

discriminatory query sql server

Navigation

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

0

I have a query:

select tbAfiliado.id,tbAfiliado.nombre, tbPrograma.nombre_programa 
from tbAfiliado 
    inner join tbPrograma on tbAfiliado.id_programa=tbPrograma.id 
where tbAfiliado.id=15

which returns me several rows:

1-carlos-fi
2-juan-fu
3-clemente-fi
4-yesit-fi

What I am looking for is to make a query that shows me the rows where the column tbPrograma.nombre_programa has the same data in most registers. Do not show me:

1-carlos-fi
2-juan-fu
3-clemente-fi
4-yesit-fi

otherwise:

1-carlos-fi
3-clemente-fi
4-yesit-fi

sql sql-server

asked by julian baltan 17.05.2018 в 18:43

source

2 answers

My icons do not work in mozila firefox but in chrome SQL server dateadd

score 0 · Answer 1

SELECT 
AF.id,
AF.nombre,
 PROG.nombre_programa 
FROM tbAfiliado AS AF
 INNER JOIN tbPrograma AS PROG on AF.id_programa=PROG.id_programa 
 INNER JOIN (SELECT TOP (1) COUNT(id_programa) AS Total,id_programa FROM tbAfiliado GROUP BY id_programa  ORDER BY Total DESC) AS MAXIMO
 ON PROG.id_programa=MAXIMO.id_programa
WHERE AF.id=15

try that if it works.

score 0 · Answer 2

Maybe you can not be more orthodox but I would do it that way due to the limitations of SQL SERVER

First declares a temporary variable where you will store the name of the most repeated program:

declare @nombreProgamRepite varchar(30)

After a temporary table to store the name and the number of times it is repeated:

declare @table as table (cantidad int,nombre_Prog varchar(30))

Then we will make a count to count the times the data is repeated and the result is stored in the temporary table:

insert into @table
select top 1 count(tbPrograma.nombre_programa) cant,tbPrograma.nombre_programa 
from tbAfiliado inner join tbPrograma on tbAfiliado.id_programa=tbPrograma.id 
where tbAfiliado.id=15 group by tbPrograma.nombre_programa

At this time if you make a selection to the temporary table you would have a result like this:

cantidad   nombre_Prog
 3           fi

You just need to assign the value that repeats the most to your variable something like this:

set @nombreProgamRepite =(select nom from @table)

and now you have the name of the program that repeats itself would only have in your select assign it to the something like this:

select tbAfiliado.id,tbAfiliado.nombre, tbPrograma.nombre_programa 
from tbAfiliado inner join tbPrograma on tbAfiliado.id_programa=tbPrograma.id 
where tbAfiliado.id=15 and tbPrograma.nombre_programa=@nombreProgamRepite

I hope that's what you're looking for, I tried it at my place and if it brings me the results that you're looking for apparently