I have one table to store users and one table to store member fee payments:
The "User" column in MemberFeePayment is a reference (foreign key) to the user that have payed the fee. The "Year" column tells which year the user have payed the fee for. If a user has been a member for several years, there will be multiple rows for the user in the MemberFeePayments table.
Now I would like to write an sql query that fetches ALL users. The resulting table should have a column that tells if the users are members in a specific year (which will be provided to the query):
What is a good way to accomplish this?
I'm using MySql (5.6).
This should work if there is only one Fee Payment record per year:
SELECT U.Id, U.Name, CASE WHEN M.ID IS NULL THEN 0 ELSE 1 END AS `IsMember` FROM Users U LEFT JOIN MemberFeePayments M ON (U.Id = M.User AND M.Year = 2013)
This will work if you have multiple payments in year:
SELECT U.Id, U.Name, CASE WHEN COUNT(M.ID) > 0 THEN 1 ELSE 0 END AS `IsMember` FROM Users U LEFT JOIN MemberFeePayments M ON (U.Id = M.User AND M.Year = 2013) GROUP BY U.Id, U.Name;
Here's a link to a demo
SELECT a.Id,a.Name,if(b.id>0,TRUE,FALSE) as ismember FROM USERS a LEFT JOIN MemberFeepayments b ON a.id=b.id
check column fields and table name
Here is a way you can do it. So it will lit all the users no matter they are in the MemberFeePayments table or not and will show Yes if they are member for a particular year else No.
select x.Id, x.Name, case when x.member is not null then 'Yes' else 'No' end as IsMember from ( select u.Id, u.Name, m.User as member from Users u left join MemberFeePayments m on m.User = u.Id AND m.year = 2014 )x
OR simply as below without doing the outer select from derived table.
select u.Id, u.Name, case when m.User IS NOT NULL THEN 'Yes' else 'No' end as IsMember from Users u left join MemberFeePayments m on m.User = u.Id AND m.year = 2014
SELECT t1.id,t1.Name,t2.id FROM Users t1 LEFT JOIN MemberFeePayments t2 ON t1.id = t2.user and t2.year = 2014
t2.id will be null if there are no data in
MemberFeePayments for that user and year