CODEWITHSUNDEEP
c#sqlsql-serverdatabasejoin
user2345661
user2345661

Reputation: 425

Check and compare column values in sql server table

I have this table (Prefrences_Table)

--------------------------
|student | Preferences |
--------------------------
Stud A   |  Stud B  
Stud A   |  Stud C
Stud B   |  Stud E
Stud B   |  Stud A
Stud C   |  Stud F
Stud F   |  Stud B
--------------------------

If "Stud A" has added "Stud B" in his Preferences list, i would like to check if "stud B" has also added "stud A" in his preference, so i can add both of them in one group. How can this be done using SQL or C#?

Upvotes: 2

Views: 2755

Answers (4)

crthompson
crthompson

Reputation: 15865

You have some SQL answers, here is one in c#/linq.

var list = new List<Prefrences_Table>();

var results = (from t in list
               join t1 in list on t.student equals t1.preferences
               where
                   t.student == t1.preferences &&
                   t.preferences == t1.student &&
                   string.CompareOrdinal(t.student, t1.student) < 0
               select new {t.student, t1.student}
              );

Upvotes: 1

Linger
Linger

Reputation: 15048

Another alternative would be the following:

SELECT * FROM 
(
  SELECT PM.student, PM.Preferences,
  (SELECT COUNT(student) FROM Prefrences_Table AS PI WHERE PI.Preferences = PM.student
  AND PI.student = PM.Preferences) AS CheckCross
  FROM Prefrences_Table AS PM
) AS PD
WHERE PD.CheckCross > 0

Upvotes: 1

lot
lot

Reputation: 1491

this might give you anwer to your question, field mutual will be one if both students added the other in preferences, zero otherwise

SELECT T1.student, T2.Preferences, 
(SELECT COUNT(*) FROM Prefrences_Table T2 WHERE T2.Preferences = T1.student AND T2.student = T1.Preferences) AS mutual
FROM Prefrences_Table T1

Upvotes: 3

Derek
Derek

Reputation: 23228

A self-join should work just fine here. The additional predicate returns only the first instance of the match to avoid duplicates.

select t.student, t1.student
from 
  Prefrences_Table t
  inner join Prefrences_Table t1
    on t.student = t1.preferences
       and t.preferences = t1.student
       and t.student < t1.student

Upvotes: 4

Related Questions