ARr0w ARr0w - 3 months ago 12
SQL Question

SQL - Show Data of Table1(PK) without having data in Table2(FK)

there are two tables Customer and Fees

(PK customer.CustomerID -> Fees.CustomerId FK)

there are customers in
Customer
table.
where in table
Fees
there is no data of current month.
without inserting data in Fees table of current month
i want to show the Customer details from Customer table of whom have not paid the fees of current month.

something like:

if(Convert(varchar(3),MonthFee, 109) != convert(varchar(3),getdate(),109))
BEGIN
select Customer.CustomerId as ID, Customer.CustomerName as Name, Customer.Phone as [Phone No], 'UnPaid' as [Pay Status], convert(varchar(3),getdate(),109) as [Month], YEAR(GETDATE()) as [Year]
from Customer inner join Fees on Customer.CustomerId = Fees.CustomerId Where FeeMonth = null

END


Help will be appreciated, thanks.

Answer

Use the below query..

 select Customer.CustomerId as ID,  
   Customer.CustomerName as Name, 
   Customer.Phone as   [Phone No],
    'UnPaid' as [Pay Status],   
     convert(varchar(3),getdate(),109) as [Month],   
    YEAR(GETDATE()) as [Year]
  from Customer 
  Where not exists(select 1 from  Fees 
      Where Customer.CustomerId = Fees.CustomerId 
       And    Month( FeeMonth )= month(getdate())
       And year(FeeMonth)=year(getdate())