Double query in 1 query

Navigation
0

I have 2 tables one of users and one of subscribers, I have an insert that sends to the subscribers table the email of the user that registered and the name of the channel

1st users 

id | usuario  | meucanal 
1  | [email protected]  | portados fundos 
2  | [email protected]  | parafernalha
3  | [email protected]  | Ei nerd
4  | [email protected]  | Canal Nostalgia

2nd inscricoes 

id | usuario | se_inscreveu_no_canal
1  | [email protected]   | Ei nerd
2  | [email protected]   | parafernalha

In short, what I need is to do a SELECT list only the channels that the user has not yet registered in the case of the example [email protected] needs to be displayed only the Nostalgia Channel 

$sql = "SELECT * FROM usuarios WHERE canal NOT EXIST IN(SELECT * FROM inscricoes WHERE o_usuario NOT IN se_inscreveu_no_canal) ORDER BY RAND();

I'm not a professional in the area, just programming as a hobby but things simpler this type of consultation is more advanced if you can help me on a path to the solution since I thank you.

Follow the structure for a better understanding (well summarized)

cadastro.php

 enter your email and the name of your channel

INSERT INTO users (user, channel) VALUES ('$ _POST [email]', $ _ POST [channel] ') "; REGISTRATION READY

listarcanais.php (the user in this part will already be logged in $ user = session [email]) SELECT * FROM users ORDER BY channel LIMIT 1 "; echo "CLICK HERE TO REGISTER";

inscribe.php (the user in this part will already be logged in $ user = session [email]) $ channel = $ GET [channel]; INSERT INTO inscripts (user, channel) VALUES ('$ user, $ channel') "; // redirect to listarcanais.php PRONTO JA CONSTS NO BD THAT THE USER HAD INSCRIBED ON THAT CHANNEL

listarcanais.php

asked by anonymous 31.03.2018 / 05:03

2 answers

-1
SELECT
    distinct(canal)
FROM
    usuarios
WHERE
    email <> '$email'
    AND
    canal NOT IN (
        SELECT canal FROM inscricoes WHERE email = '$email'

   )
    
02.04.2018 / 23:36
-2

Only these two tables exist, is there no channel table? How are all existing channels registered?

There are three tables: Usuarios , Canais and Inscricoes , where Inscricoes is many-to-many relationship between Usuarios and Canais , so just bring Canais exist in Inscricoes by Usuarios . 

select u.nome as usuario, c.nome as canal
from usuarios u, canais c
where not exists (select 1 from inscricoes i where i.cod_canal =c.cod_canal and i.cod_usuario = u.cod_usuario);
    
31.03.2018 / 05:43
Site optimization - Leverage browser cache Appear update equal to facebook, at the time