Question 21796 in Mysql Question posted in
The official MySQL documentation can be found here.

Join multiple tables with Having expression – Mysql

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
Tags:

2

Answers


  1. 0

    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
      );
    Login or Signup to reply.
  2. 0

    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;

    https://dbfiddle.uk/P9Y4RHfu

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search