Member Avatar for dharam_05

Hi,
I just want to retrieve name of the employees who have two or more than two email_id in employee table. pls help me out..

  • 3 Contributors
  • 2 Replies
  • 104 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by varmadba

Dani AI

Generated

Short answer: it depends on how emails are stored. As correctly asked, the SQL changes if emails are in a separate child table, in multiple columns on the same row, or packed in a single string. was on the right track thinking of grouping, but you must group the email rows (or count distinct email values) — not simply group the employee table rows.

Preferred (normalized) schema — one email per row:

-- employees(id, name); emails(id, employee_id, email)
SELECT e.name
FROM employees e
JOIN emails em ON em.employee_id = e.id
GROUP BY e.id, e.name
HAVING COUNT(DISTINCT em.email) >= 2;

Use COUNT(DISTINCT ...) to ignore duplicate addresses and put an index on emails(employee_id) for speed.

If the table has fixed email columns (email1, email2, ...):

SELECT name
FROM employees
WHERE ((email1 IS NOT NULL AND email1 <> '') +
       (email2 IS NOT NULL AND email2 <> '') +
       (email3 IS NOT NULL AND email3 <> '')) >= 2;

This uses MySQL treating boolean expressions as 1/0 to count non-empty emails without many OR conditions.

If multiple emails are stored in one text field (CSV or similar), count separators or occurrences of @ (simple heuristic):

SELECT name
FROM employees
WHERE (LENGTH(email_list) - LENGTH(REPLACE(email_list, '@', ''))) >= 2;

Caveats: this can be fooled by malformed data. If emails are stored as JSON arrays, use JSON_LENGTH(...) (MySQL 5.7+) to count elements. For large datasets, normalize to an emails table and add constraints (unique email if desired) for correctness and performance.

References: MySQL string functions for the LENGTH/REPLACE trick — MySQL string functions.

Recommended Answers

All 2 Replies

Member Avatar for dickersonka

select * from employees
group by employee_id
having count(employee_id) >= 2

Member Avatar for varmadba

your question is not clear

How are emails stored in table?
do you have separate columns to save each email ids
or they are saved in 1 columns

if email ids are saved in separate columns
then try

select * from table1 where
(email_column1 is not null and (email_column2 is not null or email_column3 is not null .....)

if email ids are saved in single column
then try

select *  from table1
where email_column1 like '%@%@%@%.........
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.