There are 2 tables Contacts & BlockedEntries
Table: Contact
Id
FirstName
LastName
Email
JobTitle
Table: BlockedEntries
Id
Email
Trying to find the entries from contact table with more than one occurence of blockedEntries
SELECT email, COUNT(*) as cc, GROUP_CONCAT( id SEPARATOR '#') AS ContactIds
FROM contacts
where email IN (SELECT email FROM BlockedEntries)
GROUP BY email
HAVING COUNT(*) > 1
Is there any way to get some more additional details like first name ,last Name, email,job title etc for the entries with count is more than 1
Unfortunately there is no relation between these 2 tables and email is the only possible mapping . There can be 1 or more entries present in COntact table with same email address
Sample data
Id FirstName LastName Email JobTitle
12 sam j [email protected] engineer
23 bos j [email protected] accountnt
34 cas j [email protected] engineer
33 xxx j [email protected] fied
55 dfe c [email protected] student
Table: BlockedEntries
Id Email CreateDate
1 [email protected] 09/12/2020 19:30:20
2 [email protected] 09/12/2020 19:30:20
3 [email protected] 09/12/2020 19:30:20
Result expecting
email id firstname lastName jobtitle
[email protected] 12 sam j engineer
[email protected] 34 bos j accountnt
[email protected] 23 cas j engineer
[email protected] 33 xxx j fied
[email protected] only 1 instance and no need to add this to result set
CodePudding user response：
On MySQL 8 , I would use COUNT() as an analytic function here:
WITH cte AS (
SELECT *, COUNT(*) OVER (PARTITION BY email) email_cnt
FROM contacts
)
SELECT c.Id, c.FirstName, c.LastName, c.Email, c.JobTitle
FROM cte c
WHERE email_cnt > 1 AND
EXISTS (
SELECT 1
FROM BlockedEntries be
WHERE be.email = c.email
);
CodePudding user response：
Based on your data example you just need two inner joins, one with the BlockedEntries which will get you all the related emails between BlockedEntries and Contact table and another join with a subquery which will get the emails more than once on Contact table.
Try:
select c.Id,
c.FirstName,
c.LastName,
c.Email,
c.JobTitle
from Contact c
inner join BlockedEntries be on be.Email=c.Email
inner join (select Email
from Contact
group by Email
having count(*)>1
) as cnt on cnt.Email=c.Email;