SQL - Using Distinct

Question

SQL - Using Distinct

Navigation

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

0

Friends I need a Select that loads: (all portals linked to a user and an additional column informing which portals were selected in a given news item)

So far my SQL is like this ...:

select 
   distinct 
   case when usudesc.txt_nome is null then 0 else 1 end as MeusPortais,
   case when nopo.id_noticia is null then 0 else 1 end as PotalSelecionado,
   porti.id_portal, nopo.id_noticia, porti.txt_apelido
from tb_portal porti 
   left join tb_noticiasportais nopo on nopo.id_portal = porti.id_portal 
       and nopo.id_noticia = '26' 
   left join tb_usuarioportal usu on usu.id_portal = porti.id_portal 
       and usu.id_usuario = '1' 
   left join tb_usuario usudesc on usudesc.id_usuario = usu.id_usuario
   left join tb_noticias noti on noti.id_noticia = nopo.id_noticia
order by porti.txt_apelido asc

The base has this structure:

tb_news (news_id, txt_title)

tb_noticiasportais (news_id, portal_id)

tb_portal (port_id, txt_title)

tb_userportal (userid, portal_id)

tb_user (user_id, txt_user)

mysql sql

asked by anonymous 04.02.2016 / 13:27

2 answers

Error filtering a value [duplicate] Show DIV by an ID

score 1 · Answer 1

RESOLVED:

select * from (
    select 
    distinct 
    case when usudesc.txt_nome is null then 0 else 1 end as MeusPortais,
    case when nopo.id_noticia is null then 0 else 1 end as PotalSelecionado,
    porti.id_portal, nopo.id_noticia, porti.txt_apelido
from tb_portal porti 
    left join tb_noticiasportais nopo on nopo.id_portal = porti.id_portal 
    and nopo.id_noticia = '26' 
    left join tb_usuarioportal usu on usu.id_portal = porti.id_portal 
    and usu.id_usuario = '1' 
    left join tb_usuario usudesc on usudesc.id_usuario = usu.id_usuario
    left join tb_noticias noti on noti.id_noticia = nopo.id_noticia
) as U
where U.MeusPortais = '1'

score 0 · Answer 2

select 
porti.id_portal, nopo.id_noticia, porti.txt_apelido, usudesc.txt_usuario, noti.txt_titulo
from tb_portal porti 
left join tb_noticiasportais nopo on nopo.id_portal = porti.id_portal 
and nopo.id_noticia = 26 
left join tb_usuarioportal usu on usu.id_portal = porti.id_portal 
left join tb_usuario usudesc on usudesc.id_usuario = usu.id_usuario
left join tb_noticias noti on noti.id_noticia = nopo.id_noticia
order by porti.txt_apelido asc