Reputation: 33
I was sitting several hours for trying to solve on specific problem but I couldn't get it. Finally, I decided to post it here. Here are some records,
╔════════╦════════════╦═════════╦══════════════════╗
║ AUTOID ║ PERSONNAME ║ FLOWER ║ Other columns... ║
╠════════╬════════════╬═════════╬══════════════════╣
║ 1 ║ Alex ║ Rose ║ ║
║ 2 ║ Rene ║ Rose ║ ║
║ 3 ║ Rene ║ Jasmin ║ ║
║ 4 ║ Darwin ║ Rose ║ ║
║ 5 ║ Alex ║ Rose ║ ║
║ 6 ║ Darwin ║ Jasmin ║ ║
║ 7 ║ Alex ║ Rose ║ ║
║ 8 ║ Rene ║ Jasmin ║ ║
║ 9 ║ Hello ║ World ║ ║
║ 10 ║ Hello ║ World ║ ║
╚════════╩════════════╩═════════╩══════════════════╝
How can I get this result, the person who has only one type of flower on all his/her records.
╔════════════╦════════════╗
║ PERSONNAME ║ FLOWERNAME ║
╠════════════╬════════════╣
║ Alex ║ Rose ║
║ Hello ║ World ║
╚════════════╩════════════╝
the best one I tried was this query below, and still not working.
SELECT DISTINCT t1.PersonName, t1.Flower
FROM TableName t1
INNER JOIN
(
SELECT personname, COUNT(DISTINCT flower) totals
FROM tableName
GROUP BY personname, Flower
) t2 ON t1.personname = t2.personname and
t2.totals = 1
Any Idea?
Upvotes: 3
Views: 142
Reputation: 33935
or, because there's always more than one way to skin a cat...
SELECT x.*
FROM tablename x
LEFT
JOIN tablename y
ON y.personname = x.personname
AND ((y.flower <> x.flower)
OR (y.flower = x.flower AND y.autoid < x.autoid))
WHERE y.autoid IS NULL;
Upvotes: 0
Reputation: 263933
you can use GROUP BY
clause, HAVING
clause and COUNT()
on this problem, no need to join on a subquery
SELECT PersonName, MAX(Flower) flowerName
FROM TableName
GROUP BY PersonName
HAVING COUNT(DISTINCT Flower) = 1
Upvotes: 4