Is there a way in mysql to check one specific column and see if the value occurs more then one time, and if so get the row.
lets say that my table looks like this:
id | first_name | last_name | age
1 john doe 20
.. ....... ..... ...
.. ....... ..... ...
So I want the mysql to go and bring back a list where the age is 18 AND where the first_name occurs more then once.
SELECT y.id, y.first_name, y.last_name, y.age
FROM (SELECT first_name
FROM YourTable
WHERE age = 18
GROUP BY first_name
HAVING COUNT(*) > 1) t
INNER JOIN YourTable y
ON t.first_name = y.first_name
WHERE y.age = 18;
You can use a JOIN:
SELECT DISTINCT T1.first_name
FROM yourtable T1
JOIN yourtable T2
ON T1.id < T2.id
AND T1.first_name = T2.first_name
AND T2.age = 18
WHERE T1.age = 18
t think that it is possible in MySQL without HAVING! Ill use it my heave load project! If it possible, I`ll set you more than one +1 vote!
select id, first_name, last_name, age
from YourTable where first_name in
(
select first_name
from YourTable
where age = 18
group by first_name
having count(first_name) > 1
)
