MySQL LEFT JOIN with multiple conditions

Question

I'm trying to create a query that must returns the values from table Share_users_types that do not exists in table Shares according to some conditions.

Share_types table
----------------
ID | Description
1    'Anual'
2    'Monthly'

Share_users_types table
-----------------------------
ID | user_id  | share_type_id
1    1          1
2    1          2
3    2          1

Shares table
--------------------------------------------
ID | user_id  | share_type_id | Year | Month 
1    1          1               2015   null
2    1          2               2015   05
3    1          1               2016   null
4    2          1               2015   null

If I set the following conditions in my query, it should return from the table Share_users_type:

1. Year 2016, Share type Anual -> 1 results -> User_id: 2
2. Year 2015, Share type Anual -> 0 results
3. Year 2017, Share type Anual -> 2 results -> User_id: 1 and 2
4. Year 2017, Month 06, Share type Monthly -> 1 result -> User_id: 1

With this said I have tried without any success the following queries:

SELECT sharetype.user_id 
FROM share_users_types AS sharetype
LEFT JOIN shares AS share ON share.user_id = sharetype.user_id
WHERE share.user_id IS NULL AND
share.year != '2016' AND share_type_id = 1

SELECT sharetype.user_id 
FROM share_users_types AS sharetype
LEFT JOIN shares AS share ON share.user_id = sharetype.user_id
WHERE share.year = '2016' AND share_type_id = 1

Answer 1

Use not in

    SELECT sharetype.user_id 
    FROM share_users_types AS sharetype
    where sharetype.user_id not in  (select  user_id from share where share.year != '2016' AND share_type_id = 1);

Answer 2

Getting results for 2015 - 1016 are relatively easy. First you need to create a carthesian join of user_id - share_type_id and year values and left join shares table on this resultset:

select distinct sut.user_id, sut.share_type_id, s.year
from (Share_users_types sut
join shares s)
left join shares s2
    on sut.user_id=s2.user_id and sut.share_type_id=s2.share_type and s.year=s2.year and s2.year is null

The problem is if you add 2017 or any other year to the mix, that do not exist in the shares table. Mysql in itself cannot select a value that is not there.

Either you need to create a calendar table, or use a series of unions to list all years and combine this with the Share_users_types table.

See this SO topic for guidance on this.