I'm having difficulties with creating a sql query that selects a joining of 2 tables where the relationship doesn't exists in a third table.
To be more specific I try to explain it with an example. I have a table member, form and member_form. When a member inserts an answer in the form, it's saved in the member_form table. The forms can be active, and have a deadline. So what I want is to return a result for each member and the form he still didn't fill in. But the form must be active, and deadline must be for example 2018-03-15. I don't know the amount of members or forms. So I can't say from select * where member id is = x. This is needed to send a reminder mail to all the users that didn't fill in the form yet for a specific date.
member
id | name | email
--------------------------
1 | Test | [email protected]
2 | test2 | [email protected]
4 | test4 | [email protected]
5 | test5 | [email protected]
6 | test6 | [email protected]
7 | test7 | [email protected]
form
id | insert_date | deadline_date | active
---------------------------------------------------------------
1 | 2018-03-15 00:00:00 | 2018-03-15 00:00:00 | 1
2 | 2018-02-10 00:00:00 | 2018-05-15 00:00:00 | 0
3 | 2018-03-15 00:00:00 | 2018-03-15 00:00:00 | 1
5 | 2018-03-15 00:00:00 | 2018-06-15 00:00:00 | 1
6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
7 | 2018-03-15 00:00:00 | 2018-04-15 00:00:00 | 0
member_form
member_id | form_id | answer
--------------------------------------
1 | 6 | 1
1 | 2 | 2
1 | 5 | 1
2 | 2 | 1
2 | 3 | 1
4 | 6 | 2
5 | 6 | 3
5 | 7 | 2
6 | 1 | 2
7 | 2 | 1
Result
member_id | name | email | form_id | insert_date | deadline_date | active
-------------------------------------------------------------------------------------------------------------------
2 | test2 | [email protected] | 6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
6 | test6 | [email protected] | 6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
7 | test7 | [email protected] | 6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
SELECT * FROM form AS f , member AS m WHERE f.deadline_date = '2018-05-15 00:00:00' AND f.active = 1 and !exists(select * FROM member_form AS amf WHERE amf.member_id = am.id And amf.form_id = af.id);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments