SQL query to see user with more confirmed friend requests

Question

SQL query to see user with more confirmed friend requests

Navigation

#1 by (2 votes)

0

In a database exercise I get a DB with which I must take 2 tables TBL_AMIGOS and TBL_ESTATUS_SOLICITUDES to make a query to know the user with the largest number of confirmed requests.

I've tried with HAVING and COUNT but I'm using it badly.

TBL_AMIGOS

CODIGO_USUARIO
CODIGO_AMIGO
CODIGO_ESTATUS
CODIGO_TIPO_RELACIO

TBL_ESTATUS_SOLICITUDES

CODIGO_ESTATUS
NOMBRE_ESTATUS

The tables to be used are in the upper left corner of the relational model.

For a friendship to be confirmed in the record of TBL_AMISTADES the CODIGO_ESTATUS must be equal to 1 which means CONFIRMADA as seen in the image. In case what I need is to know that USER has the most amount of 1 in the field CODIGO_ESTATUS .

Thank you.

sql oracle oracle-11g

asked by Anthony Milan 16.10.2016 в 09:13

source

1 answer

Visual Studio does not interpret a class that 'inherits' from Form as such How to use OfType

score 2 · Accepted Answer

A simple way to achieve this is by ordering the results by number of records with CODIGO_ESTATUS = 1 (or NOMBRE_ESTATUS = 'CONFIRMADA' ), and then using where rownum < 2 to limit the query to the first result.

select codigo_usuario
  from (select a.codigo_usuario
          from tbl_amigos a
          join tbl_estatus_solicitudes e
            on e.codigo_estatus = a.codigo_estatus
           and e.nombre_estatus = 'CONFIRMADA'
         group by a.codigo_usuario
         order by count(*) desc)
 where rownum < 2

And to show the user with fewer friends would be simply changing the "desc" to "asc", like this:

select codigo_usuario
  from (select a.codigo_usuario
          from tbl_amigos a
          join tbl_estatus_solicitudes e
            on e.codigo_estatus = a.codigo_estatus
           and e.nombre_estatus = 'CONFIRMADA'
         group by a.codigo_usuario
         order by count(*) asc)
 where rownum < 2