左转助学金

Question

我这里有个问题。我有四张桌子，分别是:学生、费用、支付和班级。

学生

(student_id，姓，手机，电子邮件，地址，照片文件，class_id)

班级

(class_id，class_description，class_date，class_venue)

付款

(payment_id，student_id，payment_date，payment_amount，fee_id)

费用

(fee_id，fee_description，class_id，fee_amount)

基本上，我需要创建一个查询，该查询可以显示学生是否基于payments创建了fees.但是，我已经尝试了从堆栈溢出中获得的所有剩下的联接，这些连接仍然无法得到我想要的结果。

我有这个：

SELECT 
students.student_id, fee.fee_id, fee.fee_description,
fee.class_id, fee.fee_amount, students.firstname, students.lastname,
payments.payment_id, payments.payment_date 

FROM students 
LEFT JOIN class ON students.class_id = class.class_id 
LEFT JOIN fee ON fee.class_id = class.class_id 
LEFT JOIN payments ON payments.student_id = students.student_id

我得到的结果如下：

+-----------+--------+---------------------+-----------+-------------+-----------+----------+------------+--------------+
|student_id | fee_id | fee_description     | class_id  | fee_amount  | firstname | lastname | payment_id | payment_date |
+-----------+--------+---------------------+-----------+-------------+-----------+----------+------------+--------------+
|     1     |   1    | Secondary Malay@Dec |     1     |     50      |  Bob      |   Smith  |     1      | 2013-12-18   |
|     1     |   1    | Secondary Malay@Dec |     1     |     50      |  Bob      |   Smith  |     11     | 2013-12-27   |
|     1     |   5    | Secondary Malay@Jan |     1     |     50      |  Bob      |   Smith  |     1      | 2013-12-18   |
|     1     |   5    | Secondary Malay@Jan |     1     |     50      |  Bob      |   Smith  |     11     | 2013-12-27   |
|     2     |   2    | Primary Eng@Dec     |     2     |     35      |  Joe      |   Doe    |     10     | 2013-12-02   |
|     11    |   1    | Secondary Malay@Dec |     1     |     50      |  Bryant   |   Quan   |     8      | 2013-12-19   |
|     11    |   5    | Secondary Malay@Jan |     1     |     50      |  Bryant   |   Quan   |     8      | 2013-12-19   |
+-----------+--------+---------------------+-----------+-------------+-----------+----------+------------+--------------+

问题：

• 学生(鲍勃史密斯)出现了4次(应该只出现两次- Dec和Jan )。
• 学生(布莱恩特·泉)只支付了Dec 的费用，但查询显示他也支付了Jan的费用。
• payment_id应该是唯一的，所以不能出现超过1次。

目前，我正在开发一个使用Flash Builder的移动应用程序。该应用程序是跟踪学生的付款，因此，我需要解决这个问题，然后再去其他。如果有人知道怎么做，请指导我。提前谢谢><“

源代码mysql代码在这里：http://sqlfiddle.com/#!2/d4482a

Answer 1

加入feeid上的支付和费用表，它应该给出正确的结果。

编辑：

SELECT 
students.student_id, fee.fee_id, fee.fee_description,
fee.class_id, fee.fee_amount, students.firstname, students.lastname,
payments.payment_id, payments.payment_date 

FROM students 
LEFT JOIN class ON students.class_id = class.class_id 
LEFT JOIN fee ON fee.class_id = class.class_id 
LEFT JOIN payments ON payments.student_id = students.student_id and Payments.fee_id = fee.fee_id

这将给您所有已付费和未付费的学生，您可以使用payment_id为空或不为空的位置进行筛选，这取决于您的需要，如果payment_id为空，则表示他们没有支付，否则支付。

Answer 2

试试这个。

SELECT a.student_id,d.fee_id, d.fee_description, b.class_id,d.fee_amount
from Students a, Class b, Payments c, Fee d 
where a.class_id=b.class_id and b.class_id=d.class_id and a.student_id=c.student_id
and c.payment_amount=d.fee_amount;

Answer 3

加入类到有什么意义呢？虽然我还没有测试过这个

SELECT 
students.student_id, fee.fee_id, fee.fee_description,
fee.class_id, fee.fee_amount, students.firstname, students.lastname,
payments.payment_id, payments.payment_date     

任一种

FROM students 
LEFT JOIN payments ON payments.student_id = students.student_id
LEFT JOIN fee ON fee.fee_id = payments.fee_id 
LEFT JOIN class ON students.class_id = class.class_id

如果想要有我的收费表，请检查这个

FROM students 
LEFT JOIN payments ON payments.student_id = students.student_id
LEFT JOIN class ON students.class_id = class.class_id
LEFT JOIN fee ON fee.fee_id = payments.fee_id AND fee.class_id = class.class_id