MySQL - 如何选择多次出现主键的行和另一个字段中的至少一个匹配？

Question

I have 2 tables (registrants and webcasts) containing information from people who attended webcasts for my company. The registrants table shows information about registrants from a webcast, including their email address, name, the webcast title, webcast client, etc. The webcasts table shows detailed information about the webcast, including the webcast title, date, and client.

Our clients want to see when users have attended more than one of their webcasts. I am displaying this information in conjunction with a dashboard of analytics from a specific webcast. What I need is to be able to find users in the table who:
1. Attended multiple webcasts for that company
2. At least one of the webcasts they attended must be the one for the dashboard being displayed

Right now, I have the code to find users who attended more than one webcast for that company. What I am missing is the code to filter and show users who attended more than one webcast for that company AND at least one webcast is the current webcast.

Let's use this example with company name "Company" and webcast title "Learning About Company"

SELECT *
        FROM registrants
        INNER JOIN webcasts ON registrants.WebcastTitle = webcasts.WebcastTitle
        INNER JOIN (
            SELECT Email, Client
        FROM registrants
            GROUP BY Email, Client
            HAVING count(*) > 1
        ) dup ON registrants.Email = dup.Email
        WHERE registrants.Client = 'Company' AND registrants.FirstName != ''
        ORDER BY registrants.Email;

This will return all the users that have attended multiple webcasts with that company. Can anyone help me on the rest?

Thanks!

UPDATE

Thanks to some help from FuzzyTree below, I have a working query. For anyone interested with a similar problem, this is what I've come up with:

SELECT *
FROM registrants
INNER JOIN webcasts ON registrants.WebcastTitle = webcasts.WebcastTitle
INNER JOIN (SELECT registrants.Email
FROM registrants
WHERE registrants.Client = 'Salient' AND registrants.FirstName != '' 
GROUP BY registrants.Email
HAVING COUNT(*) > 1
AND SUM(registrants.Client = 'Salient' AND 
    registrants.WebcastTitle = 'North American Crude Oil Independence: Not Just a Pipe Dream') > 0) t1
    ON t1.Email = registrants.Email
WHERE registrants.Client = 'Salient'
ORDER BY registrants.Email

Answer 1

If you want to select all registrants who have attended more than 1 webcast along with the information of one of the webcasts they attended (and you don't care which one), then you can use mysql's extended group by functionality, which lets you select columns not in the group by clause.

SELECT *
FROM registrants
INNER JOIN webcasts ON registrants.WebcastTitle = webcasts.WebcastTitle
WHERE registrants.Client = 'Company' AND registrants.FirstName != ''
GROUP BY registrants.Email
HAVING COUNT(*) > 1
AND SUM(webcasts.WebcastTitle = 'The Learning Company') > 0
ORDER BY registrants.Email;

If you want all registrant event pairs where the registrant has at least attended 1 event titled 'The Learning Company' of 'Company' and has at least attended 2 total events. Since you're joining on registrants.WebcastTitle, I'm assuming Email in registrants is not unique.

SELECT *
FROM registrants
INNER JOIN webcasts ON registrants.WebcastTitle = webcasts.WebcastTitle
INNER JOIN (SELECT registrants.Email
FROM registrants
WHERE registrants.FirstName != ''
GROUP BY registrants.Email
HAVING COUNT(*) > 1
AND SUM(registrants.Client = 'Company' AND 
    registrants.WebcastTitle = 'The Learning Company') > 0) t1
    ON t1.Email = registrants.Email