MySQL在4个表中选择数据（多个条件）

Thanks to another user I was finally able to collect some data using this query:

感谢另一位用户,我终于可以使用此查询收集一些数据了:

SELECT r.form, s.value as email
FROM subrecords s join
     records r
     on s.record = r.id AND r.name = 'question-form'
WHERE s.title = 'email'
GROUP BY s.value, r.form

details on the tables involved in the above query are found at Finding duplicates in MYSQL table where data is in multiple tables (multiple conditions needed)

有关上述查询中涉及的表的详细信息,请参阅在MYSQL表中查找重复项,其中数据位于多个表中(需要多个条件)

With the above query I get the list of emails that submitted a specific form.

通过上述查询,我​​获得了提交特定表单的电子邮件列表。

I would need now to find out which of those email addresses is subscribed to a specific mailing list, Using the "s.value" of the above query which lists email addresses

我现在需要找出哪些电子邮件地址订阅了特定的邮件列表,使用上面查询的“s.value”列出了电子邮件地址

I first need to find out the subscriber.subid which identifies each unique subscriber and their email address, which is where I would join the result from the query above

我首先需要找出subscriber.subid,它标识每个唯一订阅者及其电子邮件地址,这是我将加入上述查询结果的地方

table -> subscriber schema

subid | email

Then select from the following table WHERE listid = '33'

然后从下表中选择WHERE listid ='33'

table -> listsub schema

listid | subid | subdate | unsubdate | status

Thank you so much everyone for the incredible help!

非常感谢大家的不可思议的帮助!

1 个解决方案

#1

Here is a way by doing more joins:

这是一种做更多连接的方法:

SELECT r.form, s.value as email,
       (case when max(l.listid is not null) then 'YES' else 'NO' end) as InList33
FROM subrecords s join
     records r
     on s.record = r.id AND r.name = 'question-form' left outer join
     subscriber_schema ss
     on ss.email = s.value left outer join
     listsub l
     on ss.subid = l.subid and
        l.listid = '33'
WHERE s.title = 'email'
GROUP BY s.value, r.form;