问题描述:

I have one table to store users and one table to store member fee payments:

Users

  • Id (int)
  • Name (string)

MemberFeePayments

  • Id (int)
  • User (int)
  • Year (int)

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):

Result

  • Id (int)
  • Name (string)
  • IsMember (bool)

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

网友答案:

Try this:

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 

DEMO

网友答案:

Use left join

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

相关阅读:
Top