我正在尝试选择不在另一个表中的行。在这里我使用了left join with is null，但没有得到预期的结果。它只对一个表有效，但对另一个表无效。
主要是我不能管理四个表之间的关系。 fee -包括费用类别）和 fee_tm -包括支付费用的月份列表）， cls_fee -各等级的收费标准）和 invoice -包含有关已付费用的信息）。
因此，我试图显示学生名单谁还没有支付，或那些学生的记录，这是不在 invoice table。
mysql数据库

SELECT
    fee_tm.id AS ftm_d,
    fee.id AS f_id,
    fee_tm.en_ttl AS f_tm,
    fee.en_ttl AS fee,
    cls_fee.fee AS f_mnt
FROM
    fee
LEFT JOIN
    fee_tm ON fee_tm.year = fee.year
LEFT JOIN
    cls_fee ON cls_fee.fee_id = fee.id
LEFT JOIN
    student ON student.cls = cls_fee.c_id AND student.sec = cls_fee.s_id
LEFT JOIN
    invoice ON invoice.stu_id = student.id AND invoice.fee_id = fee.id AND invoice.ftm_id = fee_tm.id
WHERE
    student.id =1 AND invoice.ftm_id is NULL AND invoice.fee_id is NULL

当前结果

ftm_d   |   f_id    |   f_tm    |   fee     |   f_mnt
=====================================================
2       |   1       |   Feb     |   Annual  |   1000
2       |   2       |   Feb     |   Monthly |   560

预期结果

ftm_d   |   f_id    |   f_tm    |   fee     |   f_mnt
=====================================================
2       |   2       |   Feb     |   Monthly |   560

我的结果只是检查 f_tm 列，所以在我当前的结果月份 Jan 已过滤，但必须检查 fee 列和 Annual 必须筛选行。
所以，我们可以知道那些没有交学费的学生。如果在中找到记录 invoice 表中，则应在结果中筛选此费用类别。
数据库结构
学生

id  |   en_ttl  |   cls |   sec |   year
========================================
1   |   John    |   1   |   1   |   1

cls公司

id  |   en_ttl  |   year
========================
1   |   One     |   1

秒

id  |   en_ttl  |   year
========================
1   |   A       |   1

费用

id  |   en_ttl  |   year
========================
1   |   Annual  |   1
2   |   Monthly |   1
3   |   Library |   1

费用

id  |   en_ttl  |   year
========================
1   |   Jan     |   1
2   |   Feb     |   1

cls\U费用

id  |   c_id    |   s_id    |   fee_id  |   fee
===============================================
1   |   1       |   1       |   1       |   1000
2   |   1       |   1       |   2       |   560

发票

id  |   stu_id  |   fee_id  |   ftm_id
======================================
1   |   1       |   1       |   1

表的ddl语句

CREATE TABLE `student` (
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `en_ttl` varchar(100) NOT NULL,
 `cls` int(2) NOT NULL,
 `sec` int(2) NOT NULL,
 `year` int(1) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `cls` (
 `id` int(2) NOT NULL AUTO_INCREMENT,
 `en_ttl` varchar(50) NOT NULL,
 `year` int(2) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `sec` (
 `id` int(2) NOT NULL AUTO_INCREMENT,
 `en_ttl` varchar(50) NOT NULL,
 `year` int(2) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `fee` (
 `id` int(2) NOT NULL AUTO_INCREMENT,
 `en_ttl` varchar(50) NOT NULL,
 `year` int(2) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `fee_tm` (
 `id` int(2) NOT NULL AUTO_INCREMENT,
 `en_ttl` varchar(50) NOT NULL,
 `year` int(2) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `cls_fee` (
 `id` int(2) NOT NULL AUTO_INCREMENT,
 `c_id` int(2) NOT NULL,
 `s_id` int(2) NOT NULL,
 `fee_id` int(2) NOT NULL,
 `fee` int(6) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `fee` (`c_id`,`s_id`,`fee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE `invoice` (
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `stu_id` int(4) NOT NULL,
 `fee_id` int(11) NOT NULL,
 `ftm_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;