CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

MySql select users added by a user

I have a table with fields and data as below

create table connections(cn_from varchar(50), cn_to varchar(50), cn_status tinyint(1));

insert into connections(cn_from, cn_to, cn_status) values (111,222,1);
insert into connections(cn_from, cn_to, cn_status) values (111,333,1);
insert into connections(cn_from, cn_to, cn_status) values (111,444,1);
insert into connections(cn_from, cn_to, cn_status) values (111,555,1);
insert into connections(cn_from, cn_to, cn_status) values (222,333,1);
insert into connections(cn_from, cn_to, cn_status) values (222,444,1);
insert into connections(cn_from, cn_to, cn_status) values (555,222,1);
insert into connections(cn_from, cn_to, cn_status) values (666,111,1);
insert into connections(cn_from, cn_to, cn_status) values (777,111,1);
insert into connections(cn_from, cn_to, cn_status) values (121,111,1);

select cn_from, cn_to from connections where (cn_from = 111 and cn_status = 1) or (cn_to = 111 and cn_status = 1) ;

How do I write a select to find users added by user 111 and users who have added user 111?

In this case, the result for user 111 need to be:

222
333
444
555
666
777
121

Upvotes: 0

Views: 36

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Presumably, you want to eliminate duplicates, so each easer is in the result set only once. Here is a shortish version:

SELECT DISTINCT COALESCE(NULLIF(c.cn_from, 111), c.cn_to) as result
FROM connections c
WHERE 111 in (cn_from, cn_to);

The COALESCE(NULLIF()) is equivalent to:

SELECT DISTINCT (CASE WHEN cn_from = 111 THEN cn_to ELSE cn_from END) as result

Upvotes: 0

rathodbhavikk
rathodbhavikk

Reputation: 416

This is also an option, with single SELECT Query

SELECT CASE WHEN (connections.cn_from = 111) THEN connections.cn_to ELSE connections.cn_from END as `result`
FROM `connections`
WHERE `connections`.`cn_from` = 111 OR 
      `connections`.`cn_to` = 111

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

you can use union for this requirement.

select cn_to from connections where cn_from = '111'
union all
select cn_from from connections where cn_to = '111'

Upvotes: 1

Related Questions