CODEWITHSUNDEEP
sql
Stack User
Stack User

Reputation: 1398

How to find Id which is used cross

Assume that there is a table which is called Stack;

Id       StackId
-------------------
.             .
1             10
2             12
3             10
4             10
5             11
11            5
.             .
.             .

how to learn cross id like ?

Id = 5
StackId = 11

Id = 11
StackId = 5

I could not write the sql sentences which shows me 

5,11
11,5

I dont know the numbers because the table has 1.000.000+ rows, so I want to find rows which likes 5,11

Upvotes: 0

Views: 85

Answers (4)

Colin 't Hart
Colin 't Hart

Reputation: 7729

Another solution:

select *
from Stack s
where exists (select 1 from Stack where Id = s.StackId and StackId = s.Id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can approach this with aggregation as well as a join. Here is one method:

select (case when id < StackId then id else StackId end) as FirstVal,
       (case when id < StackId then StackId else id end) as SecondVal
from t
group by (case when id < StackId then id else StackId end),
         (case when id < StackId then StackId else id end)
having count(distinct id) = 2

If you have a database with a least() and greatest() functions, and you know there are now duplicates in the table, you can rephrase this as:

select least(id, StackId) as FirstVal, greatest(id, StackId) as SecondVal
from t
group by least(id, StackId), greatest(id, StackId)
having count(*) = 2

Upvotes: 1

TechDo
TechDo

Reputation: 18649

Please try:

select a.* from 
    YourTable a inner join YourTable b on a.Id=b.StackId

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125640

Use JOIN to the table itself with s1.Id = s2.StackId AND s1.StackId = s2.Id condition:

SELECT s1.Id, s1.StackId
FROM Stack s1
JOIN Stack s2 ON s1.Id = s2.StackId AND s1.StackId = s2.Id

Because INNER JOIN is used (it's by default) rows with no corresponding s2 values won't be returned.

Upvotes: 3

Related Questions