Trying to find which cases relate to at least two of the names on a list SQL-CodePudding

I am trying to find which cases relate to at least two of the names on my list. All of the data is found in the same table. I would like my query to produce the list of the cases that have more than 2 of the same names related to them as well as which names they are related to. This is what I have so far:

SELECT related_persons.name
FROM related_persons
WHERE related_persons.case_number =
    (SELECT related_persons.case_number
     FROM related_persons
     WHERE related_persons.name LIKE 'A%'
    OR NAME LIKE 'B%'
    OR NAME LIKE 'C%'
    OR NAME LIKE 'D%'
    OR NAME LIKE 'E%'
    OR NAME LIKE 'F%');

I am getting an error that says more than one row returned by a subquery used as an expression.

I have also tried the following:

SELECT
    related_persons.case_number
FROM
    related_persons
GROUP by related_persons.case_number
HAVING SUM(CASE WHEN NAME LIKE 'A%' OR NAME LIKE 'B%' OR NAME LIKE 'C%' OR NAME LIKE 'D%  OR NAME LIKE 'E%'  OR NAME LIKE 'F%' THEN 1 else 0 END) >= 2;

But this gives me case number that just have 1 match.

Any suggestions?

CodePudding user response：

You could use a CTE:

WITH Names AS (
    SELECT LEFT(name, 1) AS iniLetter, COUNT(*) qty
    FROM related_persons
    GROUP BY LEFT(name, 1)
    HAVING LEFT(name, 1) IN ('B','C','D','E','F')
)
SELECT related_persons.case_number
FROM related_persons 
JOIN Names on iniLetter = LEFT(name,1)

CodePudding user response：

I don't understand what you want, so I'm guessing. However, I'm reasonably sure you can solve it by joining the table to itself finding a different name in the join.

This find cases that have two names with different starting letters, but both start with A-F:

select distinct t1.case_number
from related_persons t1
join related_persons t2 on t2.case_number = t1.case_number
  and left(t2.name, 1) between 'A' and 'F'
  and left(t2.name, 1) != left(t1.name, 1)
where left(t1.name, 1) between 'A' and 'F'

If the A-F thing is not needed and you just want to find two different names:

select t1.case_number
from related_persons t1
join related_persons t2 on t2.case_number = t1.case_number
  and t2.name > t1.name