CODEWITHSUNDEEP
sqlsql-serverdatabase
Roberto Sevilla
Roberto Sevilla

Reputation: 21

SQL - Select using values from 2 Columns that should be at least once on both

I have the following information

|----------------------| 
|       Table1         |   
|----------------------| 
| Id  | Info1 | Info2  |    
|----------------------|    
| 101 | foo   | bar    |    
| 102 | bar   | fubar  |   
| 103 | tot   | bar    |    
| 104 | fubar | foo    |    
| 105 | foo   | fubar  |    
| 106 | spur  | tot    |     
| 107 | sar   | nat    |

I need to create a query in order to only get rows where the data in Info1 is at least once in the column Info2 and the one in Info2 is at least once in Info1, meaning that the rows with "tot", "spur" and "sar" in Info1 shouldn't appear in the results, because "spur" and "sar" are only in Info1 and in the case of "tot" it is on both columns but since the row "spur" shouldn't appear then the row with "tot" in Info1 doesn't meet the criteria to be shown.

I have try with the following which almost got me what I want

SELECT  a.*
FROM    Table1 a
WHERE   EXISTS
    (
        SELECT  1
        FROM    Table1 b
        WHERE   a.Info2 = b.Info1
    ) and 
    EXISTS
    (
        SELECT  1
        FROM    Table1 c
        WHERE   a.Info1 = c.Info2
    )

But it returns me:

| Id  | Info1 | Info2  |    
|----------------------|    
| 101 | foo   | bar    |    
| 102 | bar   | fubar  |   
| 103 | tot   | bar    |    
| 104 | fubar | foo    |    
| 105 | foo   | fubar  |

Am I doing something wrong? or should I look for a different approach?

These are the expected results:

| Id  | Info1 | Info2  |    
|----------------------|    
| 101 | foo   | bar    |    
| 102 | bar   | fubar  |   
| 104 | fubar | foo    |    
| 105 | foo   | fubar  |

Upvotes: 2

Views: 78

Answers (4)

shawnt00
shawnt00

Reputation: 17935

Attempt #1

select t.*
from
    T as t
    inner join (
        select distinct Info1 as value from T t1 inner join T t2 on t2.Info2 = t1.Info1
    ) as v
        on v.value in (Info1, Info2)

Attempt #2

Seeing the requirement that both columns must also appear in the other list, I offer this suggestion. The first query solves a different problem.

select t.Id, t.Info1, t.Info2
from
    T as t
    inner join T as t2
        on t2.Info2 = t.Info1 and t2.Id <> t.Id
    inner join T as t3
        on t3.Info1 = t.Info2 and t3.Id <> t.Id
group by t.Id, t.Info1, t.Info2

I see that this still doesn't remove row 103 so I've realized that the requirement was still a little more complicated. I'll come back to this later...

Attempt #3

...Not sure this is elegant but it does work:

with
pass1(Info) as (
    select distinct t1.Info1 from T t1 inner join T t2 on t2.Info2 = t1.Info1
),
pass2(Info) as (
    select distinct case n when 1 then Info1 when 2 then Info2 end
    from T, (select 1 union all select 2) as split(n)
    where  Info1 not in (select Info from pass1)
        or Info2 not in (select Info from pass1)
)
select * from T
where   Info1 not in (select Info from pass2)
    and Info2 not in (select Info from pass2)

I use T as stand-ins for your tables true name. In my view "Table1" makes the queries look more complicated.

Upvotes: 0

void
void

Reputation: 7890

it's not difficult you just need a self join and an exists clause:

select distinct t.*
from table1 t
join table1 t1 on t.info1=t1.info2
where exists(
  select * from table1 t2
  where t2.info2=t1.info1
)

here is the demo

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SQL Fiddle Demo

SELECT DISTINCT a.*
FROM table1 a
inner join table1 b
   on (   a.Info1 = b.Info2
       or a.Info2 = a.Info1
      )
inner join table1 c
   on (   b.Info2 = c.Info1
       or b.Info1 = c.Info2
      )
  and a.Id <> c.Id

OUTPUT

|  Id | Info1 | Info2 |
|-----|-------|-------|
| 101 |   foo |   bar |
| 102 |   bar | fubar |
| 104 | fubar |   foo |
| 105 |   foo | fubar |

Big Warning

If more than 3 steps are need it may break my code 

CREATE TABLE table2
    ([Id] int, [Info1] varchar(5), [Info2] varchar(5))
;

INSERT INTO table2
    ([Id], [Info1], [Info2])
VALUES
    (101, 'tar', 'foo'),
    (102, 'foo', 'bar'),
    (103, 'bar', 'car'),
    (104, 'car', 'fish');

Upvotes: 1

CactusCake
CactusCake

Reputation: 990

How about:

SELECT *
FROM Table1
WHERE Info1 in (SELECT Info2 FROM Table1)
  AND Info2 in (SELECT Info1 FROM Table1)
  AND Id NOT IN (SELECT Id FROM Table1
                 WHERE (Info1 in (SELECT Info2 FROM Table1)
                        AND Info2 not in (SELECT Info1 FROM Table1))
                    OR (Info2 in (SELECT Info1 FROM Table1)
                        AND Info1 not in (SELECT Info2 FROM Table1))
                )

It got a bit messy with all the nested selects, but that should work.

Upvotes: 0

Related Questions