CODEWITHSUNDEEP
sqlsql-server
Subin Jacob
Subin Jacob

Reputation: 4864

How to query a pair like table structure?

Before Query

User   Friend

A      B
A      C
D      A
F      A

After Query

User   Friend

A      B
A      C
A      D
A      F

How can I get the result shown. I want to get all friends of A.

Upvotes: 0

Views: 47

Answers (3)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Without a UNION

SELECT CASE WHEN [User]='A' THEN [user] ELSE [friend] END as [A], CASE WHEN [User]='A' THEN [friend] ELSE [user] END AS [Friend Of A] WHERE [user]='A' OR [friend]='A'

Upvotes: 0

Prashant16
Prashant16

Reputation: 1526

Try this

SELECT  USER AS 'User',
        friend AS 'Friend'
FROM    t
WHERE   USER = 'A'
UNION
SELECT  friend ,
        USER
FROM    t
WHERE   friend = 'A'

Upvotes: 0

dani herrera
dani herrera

Reputation: 51675

With a union:

select user, friend 
from t
where user = 'A'
union 
select friend, user
from t
where friend = 'A'

Notice than Union behavior is distinct that is that you expect ( opposed to union all )

Upvotes: 2

Related Questions