Checking for reciprocal relationships in mysql. A trivial one table problem.

Question

I have a mysql table that stores relationships. Items can be related to another item in one direction, or both items can be related to each other.

I want to return all items related to my primary item - but I also want to check to see if the related item has a 'reverse relationship' to the current item and show this as a boolean

 |--------------|---------------|
 |    SKU       |  related_SKU  |
 |--------------|---------------|
 | 0001         |  0099         |
 | 0002         |  0099         |
 | 0099         |  0001         |
 |--------------|---------------|

If I want to get all relationships for SKU=0001

SELECT related_SKU from relationships where SKU='0001'

returns

 |--------------|
 | related_SKU  |
 |--------------|
 | 0099         |
 |--------------|

but what I want is

 |--------------|---------------|
 | related_SKU  |   reciprocal  |
 |--------------|---------------|
 | 0099         |      1        |
 |--------------|---------------|

or

 SELECT related_SKU from relationships where SKU='0002'

 |--------------|---------------|
 | related_SKU  |   reciprocal  |
 |--------------|---------------|
 | 0099         |      0        |
 |--------------|---------------|

What's the best way to do this?

Answer 1

You may want to try something like this:

SELECT r1.related_SKU,
       IF(( SELECT COUNT(*) 
            FROM   relationships r2 
            WHERE  r2.SKU = r1.related_SKU AND r2.related_SKU = r1.SKU
       ) > 0, 1, 0) AS reciprocal
FROM   relationships r1
WHERE  r1.SKU = '0001';

Test Case:

CREATE TABLE relationships (SKU int, related_SKU int);

INSERT INTO relationships VALUES (1, 99);
INSERT INTO relationships VALUES (2, 99);
INSERT INTO relationships VALUES (99, 1);

Results with reciprocal:

SELECT r1.related_SKU,
       IF(( SELECT COUNT(*) 
            FROM   relationships r2 
            WHERE  r2.SKU = r1.related_SKU AND r2.related_SKU = r1.SKU
       ) > 0, 1, 0) AS reciprocal
FROM   relationships r1
WHERE  r1.SKU = '0001';

+-------------+------------+
| related_SKU | reciprocal |
+-------------+------------+
|          99 |          1 |
+-------------+------------+
1 row in set (0.00 sec)

Results without reciprocal:

SELECT r1.related_SKU,
       IF(( SELECT COUNT(*) 
            FROM   relationships r2 
            WHERE  r2.SKU = r1.related_SKU AND r2.related_SKU = r1.SKU
       ) > 0, 1, 0) AS reciprocal
FROM   relationships r1
WHERE  r1.SKU = '0002';

+-------------+------------+
| related_SKU | reciprocal |
+-------------+------------+
|          99 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

Answer 2

select r.related_sku, recip.sku is not null as reciprocal
from relationships r
  left join relationships recip on (r.related_sku = recip.sku && recip.related_sku = r.sku)
where r.sku = '0001'