CODEWITHSUNDEEP
mysqlsql
KBrian
KBrian

Reputation: 55

Use of HAVING in MySQL

I have a table from which I need to select all persons that have a first name that is not unique and that that set should be selected only if among the persons with a similar first name, all have a different last name.

Example:

FirstN    LastN
Bill      Clinton
Bill      Cosby
Bill      Maher
Elvis     Presley
Elvis     Presley
Largo     Winch

I want to obtain

FirstN    LastN
Bill      Clinton

or

FirstN    LastN
Bill      Clinton
Bill      Cosby
Bill      Maher

I tried this but it does not return what I want.

SELECT * FROM Ids
GROUP BY FirstN, LastN
HAVING (COUNT(FirstN)>1 AND COUNT(LastN)=1))

[Edited my post after Aleandre P. Lavasseur remark]

Upvotes: 5

Views: 192

Answers (4)

Jcis
Jcis

Reputation: 173

Similar to answer 2 but getting just first register where the name repeats (the first result you said you wanted to obtain)

select T.FirstN, T.LastN from  (
      select FirstN, LastN  from Ids
    group by FirstN, LastN
      having count(1) = 1) T
group by FirstN
having count(1) > 1;

Upvotes: 0

sam yi
sam yi

Reputation: 4934

You can probably do this...

SELECT FirstN + LastN as FullName, COUNT(*)
FROM Ids
GROUP BY FirstN + LastN
HAVING COUNT(*) > 1

Be sure to check for nulls since that'll nullify the concatenation.

Upvotes: 0

Glenn
Glenn

Reputation: 9150

WITH duplicates AS (
  SELECT firstn --, COUNT(*), COUNT(DISTINCT lastn)
    FROM ids
    GROUP BY firstn
    HAVING COUNT(*) = COUNT(DISTINCT lastn)
       AND COUNT(*) > 1
)
SELECT a.firstn, a.lastn
  FROM ids a INNER JOIN duplicates b ON (a.firstn = b.firstn)
  ORDER BY a.firstn, a.lastn

If mysql does not support WITH, then inner query:

SELECT a.firstn, a.lastn
  FROM ids a
      ,(SELECT firstn --, COUNT(*), COUNT(DISTINCT lastn)
          FROM ids
          GROUP BY firstn
          HAVING COUNT(*) = COUNT(DISTINCT lastn)
             AND COUNT(*) > 1
        ) b
  WHERE a.firstn = b.firstn
  ORDER BY a.firstn, a.lastn

Upvotes: 7

TTT
TTT

Reputation: 75

can you try this :

SELECT A.FirstN, B.LastN
FROM (
SELECT FirstN
FROM Ids
GROUP BY FirstN
HAVING (COUNT(FirstN)>1)
) AS A
INNER JOIN Ids B ON (A.FirstN = B.FirstN)
GROUP BY A.FirstN, B.LastN
HAVING COUNT(B.LastN)=1

Upvotes: 3

Related Questions