Get rows that are not logged in

Question

Get rows that are not logged in

Navigation

#1 by (4 votes)

5

I have a table with columns para and de where I enter different IDs in each one, however I need to get the ID of the user that sent the request to me, ie the ID that is not the same as the user logged in.

SELECT 'username' FROM all, users WHERE ('de' = 'ID DO USUARIO LOGADO' OR 'para' = 'ID DO USUARIO LOGADO') AND (users.idu = chat.from NOT IN (ID DO USUARIO LOGADO) OR users.idu = chat.to NOT IN (ID DO USUARIO LOGADO)) GROUP BY 'c_id' order by id desc

I've created this code, but does it continue to return me the logged in user ID, not what I sent, what's happening?

I used NOT IN to prevent it from being the logged in user ID, but it still did not work.

Exemplo: 

Tabela ALL

-------------
| de | para |
-------------
|  2 |  1   |
-------------

TABELA USERS

------------------
| idu | username |
------------------
|  1  |  MARCOS  |
------------------
|  2  |  MANUEL  |
------------------

SELECIONA O ROW ONDE (para = USUARIO LOGADO or de = USUARIO LOGADO) e depois vai na tabela USERS e seleciona o usuário que não seja o usuário logado dentre 'para' e 'de'.

Ou Seja, retornaria -> Manuel

mysql sql

asked by anonymous 01.02.2015 / 23:48

1 answer

How to insert into a database with special characters? How to wait for the result of a Restful API in NodeJS

score 4 · Answer 1

As far as I understand, one of the ids ( de or para ) should be that of the logged in user, and the other not, and you want the other one in either of the two situations , right? There are several ways to do this, but the more semantically correct in my opinion would be using a union :

select username
from users
where idu in (
    select de from 'all' where para = 'ID DO USUARIO LOGADO'
    union
    select para from 'all' where de = 'ID DO USUARIO LOGADO'
);

Example in SQLFiddle . That is, select the set where the logged in user is para , and join the set where the logged in user is de . In each case, the id that interests you is the other. Look in the table users who has this id.

Update: an alternative way, without involving subquery , would simply be to change the order of their conditions; instead of a conjunction of disjunctions (i.e.% with% of% with% s) you do the opposite. This would however require a AND more than the use of OR :

select case
           when u1.idu = 'ID DO USUARIO LOGADO' then u2.username
           else u1.username
       end as username
from users u1
    join 'all' a  on u1.idu = a.de
    join users u2 on u2.idu = a.para
where
    (a.de = 'ID DO USUARIO LOGADO' AND a.para != 'ID DO USUARIO LOGADO') OR
    (a.de != 'ID DO USUARIO LOGADO' AND a.para = 'ID DO USUARIO LOGADO');

Example .