SQL where exists while preserving values between them

Question

Well, I have 2 tables like this:

Table1

ID | USER_ID  
1    0      
2    2       
3    15      
4    16

Table2

ID | FROM | TO 
9    0      2 
9    2      16 
9    16     15 
9    15     0 
10   15     2

What I want is really simple but driving me crazy, considering that ID , FROM and TO represents users in table 2. I want to get someone in FROM (which is Table1.user_id) with an ID in table2 such as it also exists in TO (which is the same Table1.user_id) with the same ID of table2

For example, record 16 is eligible. Because it appears in From with ID of 9 and as TO with the same ID of 9 in table 2 (both TO and FROM correspond to a user_id of 15 in table1)

What I have done was:

select * 
from `Table1` 
where exists (select ID from `Table2` as p1 where FROM = 16) 
      and exists (select ID from `Table2` as p2 where ID = 16) 
      and p1.ID = p2.ID

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

You could try using a self join to find records with the same ID and then compare the values.

select a.from from table1 a inner table1 b on a.id = b.id
where a.from = b.to

edited May 23 '17 at 12:28

Community

1
1

answered Feb 06 '15 at 22:56

spondee

23
1
6

1

the 'where' could also be replace with 'and' – Kevin Feb 06 '15 at 23:05

Serif Emek · Accepted Answer · 2015-02-06T23:05:35.610

0

This may work;

select * from table1 a where a.USER_ID in 
(select b.FROM from table2 b
where exists (select c.id from table2 c
where b.id = c.id and b.FROM = c.TO) )

edited Feb 06 '15 at 23:05

answered Feb 06 '15 at 22:47

Serif Emek

674
5
13

I've updated the question with more details .. please check it – Naughty.Coder Feb 06 '15 at 22:59

Gordon Linoff · Answer 3 · 2015-02-06T23:20:57.203

0

Is this what you want?

select *
from table1 t
where exists (select 1 from table2 t2 where t2.`from` = t.id) and
      exists (select 1 from table2 t3 where t3.`to` = t.id);

edited Feb 06 '15 at 23:20

answered Feb 06 '15 at 22:47

Gordon Linoff

1,242,037
58
646
786

They are two tables only, one we get the main id from (that stands for FROM and TO in 2 different records) and the other table (x) contains FROM,TO,ID ... So, I want to get table (x) ID – Naughty.Coder Feb 06 '15 at 22:52
@Naughty.Coder . . . That really doesn't change the idea behind the query. I made the changes. – Gordon Linoff Feb 06 '15 at 23:21

milan minarovic · Answer 4 · 2015-02-07T00:27:55.207

I am not sure If understand it correctly.

If User_ID from Table_1 should be present in From AND in To columns from Table_2 AND for those records also ID in Table_2 must be same,

Then for those condition will be eligible not only User_ID 16 and 15 as you mentioned in your example but also for 0 and 2.

Assuming that is correct.

Then try this code (for mySQL you probably have to change some syntax):

SELECT A.*  
FROM Table_1 AS A
INNER JOIN Table_2 AS B ON (A.USER_ID=B.FROM)
INNER JOIN Table_2 AS C ON (A.USER_ID=C.TO AND C.ID = B.ID)

SQL where exists while preserving values between them

4 Answers4