CODEWITHSUNDEEP
mysql
Dani
Dani

Reputation: 5908

Mysql Query to match two columns

I have the following table structure:

Id, SpouseId
1, 2
2, 1

Basically this tells if two people are married.

I need to to write a mysql query that returns Married couples in the database.

I thought first that i will just check if the SpouseId is not empty, but that is not enough, because i also need to check if the Id exists.

Upvotes: 1

Views: 1048

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270775

Use an INNER JOIN against itself to return pairs where both partners exist. This way, in order for the JOIN condition to be met, the spouse must not only be non-empty, but must also have a matching row as Id. Hopefully, you don't have any instances where the SpouseId of a corresponding SpouseId is not the other Id (i.e A -> married to B -> married to C)

SELECT
   p.Id AS person,
   s.Id AS spouse
From 
  people p
  INNER JOIN people s ON p.SpouseId = s.Id
/* Should eliminate the duplicate, since one pair will always have 
   a higher id for the person than the spouse, and the other is lower. */
WHERE p.person > p.spouse

Note, however, that this will return 2 rows for every pair since each partner will appear both in Id and in SpouseId.

Update

The WHERE clause was added to eliminate one of rows for each married pair. The idea is that for each pair, there's a higher id and a lower one. The pair with the higher id in person is retained, and the pair with the lower id in person is eliminated.

Upvotes: 3

Related Questions