0

Say I have table called "Everyone_Loves_Relationships" which contains three fields: Boy_Name, Girl_Name, Relationship_Status. The list goes like this:

Boy_Name Girl_Name Relationship_Status
  Tony     Carol            No
  Simon    Julia            Yes
  Frank    Carol            No
  Frank    Lucy             No
  Frank    Lucy             No
  Tony     Lucy             No
  Tony     Samantha         Yes
  Frank    Carol            Yes

Each combination can show multiple times.

You'll notice that Frank and Carol are showing twice, once with a Yes and once with a No.

How do I design a SQL request that checks for combinations of Boy_Name&Girl_Name that have both a Yes and a No?

Thank you!

Akina
  • 19,866
  • 2
  • 17
  • 21
Faust
  • 103
  • 2

1 Answers1

1
SELECT Boy_Name, Girl_Name
FROM Everyone_Loves_Relationships
GROUP BY Boy_Name, Girl_Name
HAVING COUNT(DISTINCT Relationship_Status) = 2

This query assumes that none another value (including NULL) is present in Relationship_Status field. If this is not true then replace the condition with

HAVING SUM(DISTINCT CASE WHEN Relationship_Status = 'Yes'
                         THEN 1
                         WHEN Relationship_Status = 'No'
                         THEN 2
                         ELSE 3
                         END) < 3
Akina
  • 19,866
  • 2
  • 17
  • 21
  • Awesome! Thank you so much! :) – Faust Dec 07 '19 at 18:21
  • If Relationship_Status had 10 different possible values but we wanted to only query for the combinations of names that have (either 'Yes' or 'Yesss') AND (either 'No' or Nooo'), I assume we could use your second example and tweak it? – Faust Dec 07 '19 at 18:31
  • Also, thank you for the editing my question. I'm a new member and didn't know how to properly word/format my question. – Faust Dec 07 '19 at 18:38
  • 1
    I assume we could use your second example and tweak it? Yes. For example, CASE WHEN Relationship_Status IN (1st synonims list) THEN 1 EWHEN Relationship_Status IN (2nd synonims list) THEN 2 ELSE 3 END < 1 – Akina Dec 07 '19 at 18:55
  • Perfect! Greatly appreciated. – Faust Dec 08 '19 at 00:08