-1

hello i have the database like this it has 2 tables users and payments

table payments has list of months and years when user has paid like, if the user has paid for the month of january 2019 database payments has month as 1 and year as 2019 and user is suppose to pay every month and i need to select the list of all the users who has not paid in any month .

right now i am doing it like this 

select name,month,year from users u left join payments p where 
 ( (u.usid=p.usid and subscribed='1') and 
(year ='2018' and month NOT IN ='1,2,3,4,5,6,7,8,9,10,11,12') )

Thankx

It may be little simple to do but i cant figure it out .

Amani
  • 281
  • 3
  • 15
  • 1
    Please refer to the following link for details about how to do this: https://stackoverflow.com/questions/1519272/mysql-not-in-query – Khathiravan Raj Maadhaven Jan 16 '19 at 09:34
  • 2
    I would suggest a left outer join from users to payments might help. Then you'll get all users, and only those who have a payment in will have data in the payments columns. The old-style comma-separated join syntax you're using now will create an inner join by default. But you may get multiple entries per user if they make multiple payments. So maybe instead you could get a list of all user IDs who made payments (from the payments table) and wrap that in a NOT IN clause where you've selected all usernames from the users table. That might make more sense. Have you tried anything? – ADyson Jan 16 '19 at 09:34
  • 2
    `SELECT * FROM 13c_users WHERE 13c_usid NOT IN (SELECT 13c_usid FROM 13c_payments WHERE 13c_year='2019' and 13c_month !='LIST OF MONTHS TO MATCH')` – Roy Bogado Jan 16 '19 at 10:11
  • it does not answer the question actually i cant figure it out. i have to join users table with payments table and select the row if the payment table does not have any of the months and year from last year till today . – Amani Jan 16 '19 at 10:15
  • @Amani why exactly doesn't that code help? Have you actually tried it? If so then please explain the issue. Roy's code is pretty much exactly what my second suggestion (above) would end up like. You don't necessarily _need_ a join, if that's not the best solution. If you only want users who haven't got a payment record, then joining from users to payments doesn't make much sense because there won't be any matching records in the payments table for those users. (As I said, you could use an outer join, but it's still tricky then to separate the users who have a payment from those who don't). – ADyson Jan 16 '19 at 11:42
  • This is what i tried but doesnot work ! select name,month,year from users u left join payments p where ( (u.usid=p.usid and subscribed='1') and (year ='2018' and month NOT IN ='1,2,3,4,5,6,7,8,9,10,11,12') ) Thankx – Amani Jan 17 '19 at 13:37

1 Answers1

0

Try this,

To get all the users who have not paid for any months in 2018.

SELECT * FROM `user` u left join payments p on u.usid = p.usid where usid not in (select usid from payments where `year` = 2018);

OR

SELECT * FROM `user` where usid not in (select usid from payments where `year` = 2018);
Chanuka Sandeepa
  • 680
  • 6
  • 10