CODEWITHSUNDEEP
mysqlsqldistinct
Abbie
Abbie

Reputation: 153

SQL query for finding pairs that share some values but not others

Im having some trouble creating a query for this problem.

I need to find which pairs of people have WorkedOn Build with each other but have not Financed them together.

My table:

Build(person, financed, workedOn)

Person | Financed | WorkedOn
-----------------------------
Person1| Build1   | Build4
Person2| Build2   | Build5
Person3| Build1   | Build5
Person4| Build2   | Build3
Person5| Build3   | Build1
Person6| Build1   | Build5
Person7| Build4   | Build3
Person8| Build5   | Build3

The correct query should produce something like this:

Person
-------
Person7  Person8
Person8  Person4
Person6  Person3

Ive tried using distinct and having I just dont seem to be getting it right. Any help would be appreciated, Im using sqlite3, thank you :)

Upvotes: 1

Views: 73

Answers (2)

Anton
Anton

Reputation: 4052

SELECT t1.person, t2.person
FROM build t1, build t2
WHERE t1.workedon = t2.workedon
AND t1.financed != t2.financed
AND t1.person > t2.person

SQL Fiddle

Upvotes: 2

Subin Chalil
Subin Chalil

Reputation: 3660

You can do self join.

Here is the SQLFiddle Demo

Input : enter image description here

Output: enter image description here

SELECT T1.Person,T2.Person,CONCAT(T1.Person,' ',T2.Person) AS Person
    FROM
          Build T1 
    INNER JOIN 
          Build T2 
    ON T1.WorkedOn = T2.WorkedOn AND T1.Financed <> T2.Financed 
   WHERE T1.Person < T2.Person

Hope this helps.

Upvotes: 0

Related Questions