CODEWITHSUNDEEP
mysql
Paul
Paul

Reputation: 421

mysql - WHERE records does not have records in another table

I have some tables from which I need to get data.
Here is my structure:

employees

| id |   name  |
+----+---------+
|  1 | Michael |
|  2 |  Sarah  |

reports

| id | employee_id | month | year | value | group_id |
+----+-------------+-------+------+-------+----------+
|  1 |      1      |   01  | 2018 |   35  |    1     |
|  2 |      1      |   02  | 2018 |   12  |    1     |
|  3 |      2      |   02  | 2018 |   2   |    2     |

groups

| id | name | employee_id |
+----+------+-------------+
|  1 | G11  |      1      |
|  2 | Z15  |      2      |

Now I need to get groups with employee WHERE employee with group_id AND month AND year DON'T HAVE REPORT, eg.
When I look for 01.2018, it should returns me only Z15 but when I look for 04.2018 it should return Z15 and G11.
How can I do this? At this moment I have sth like this: 

SELECT
    groups.*,
    employees.*,
    -- all fields from reports

FROM
    groups

INNER JOIN
    employees
        ON
    employees.id = groups.employee_id

Upvotes: 0

Views: 16

Answers (1)

Strawberry
Strawberry

Reputation: 33945

My column names are slightly different from yours. That's deliberate...

SELECT g.* 
  FROM groups g 
  LEFT 
  JOIN reports r 
    ON r.group_id = g.group_id 
   AND r.yearmonth = 201801 
 WHERE r.report_id IS NULL;

Upvotes: 1

Related Questions