我有两个表,但我在编写查询时遇到困难。 我试过左加入
SELECT swimmer.id, pool,last_name, first_name, paid, membership.month, datePaid, membership.fee, membership.datePaid
FROM swimmer
LEFT JOIN membership
ON membership.swimmer_id = swimmer.id
WHERE membership.month = 'September'
OR membership.month IS NULL;
当我使用此代码时,我想念那些为 8 月付款的人:
Pool FirstName LastName DatePaid Fee Method
B Sarah Delt null null null
B Pavle North null null null
S Daniel Key 2018-09-01 2000 Cash
在我的主页上,我有月份的下拉菜单。我想显示当月支付的所有游泳者以及未支付的游泳者,但它们的值应为空(不包括池、名字和第二名(
游泳桌:
NameID PoolName FirstName LastName
1 B Anna Pier
2 B Wolf Sedveh
3 B Sarah Delt
4 B Pavle North
5 S Daniel Keys
会员表:
ID Year Month DatePaid Fee Method SwimmerId(FK)
1 2017 August 2017-08-01 1000 Cash 1
2 2017 August 2017-08-01 2000 Cash 2
3 2017 September 2017-09-01 2000 Cash 5
最后,我需要这样的表格:
Pool FirstName LastName DatePaid Fee Method
B Anna Pier null null null
B Wolf Sedveh null null null
B Sarah Delt null null null
B Pavle North null null null
S Daniel Keys 2017-09-01 2000 Cash
因此,基本上,我需要一个查询来显示所选月份(和年份(的付费会员资格。对于那些付费的人 - 返回数据,对于那些没有 - 返回 null。 我希望我解释得很好。
SELECT swimmer.id, pool,last_name,
first_name, paid, membership.month,
datePaid, membership.fee, membership.datePaid
FROM swimmer
LEFT JOIN membership
ON membership.swimmer_id = swimmer.id
AND membership.month = 'September'
AND membership.year = '2017'
对于LEFT JOIN
:
- 如果要筛选
LEFT
表,请添加WHERE
条件。 - 对于
RIGHT
表,您可以添加ON
条件。