Query that calculates the state according to other tables

The type of JOIN that serves to show the mismatched rows is LEFT JOIN . When you use it, the mismatched columns acquire the value NULL . Then you can use conditional or CASE to show something else when the value is NULL .

Apart from that, there is a clear design error in your data . Why instead of having two tables to handle the payments do not use a single column that identifies the payment type (for example: 1 for cash payment; 2 for check , etc).

This allows having the most centralized information. If you want to have everything in one result right now you would have to do JOIN between three tables instead of two and this has its cost in performance. Also, what happens if the system evolves and other types of payment are added such as bank transfer, credit card, etc? Using a table for each type of payment does nothing but disperse the data and complicate the maintenance / integrity of the same.

Or, let's complicate it a bit more. What if the system evolves and are allowed to make installment payments or something like installments to the total outstanding quota?

Let's see an example applying what has been said, based on test data. With the query I also answer your other question: we will compare the fecha_vencimiento with NOW() to determine if the payment date expired or not.

I hope you find it useful:

Complete test data

VIEW DEMO IN REXTESTER

CREATE TABLE IF NOT EXISTS cuotas 
(
    cuota_id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    importe             DECIMAL(10,2),
    fecha_vencimiento   DATETIME
 )ENGINE=INNODB;

INSERT INTO cuotas (importe,fecha_vencimiento) 
    VALUES
    (1000.00,'2018-09-20'),
    (5000.00,'2018-08-20'),
    (8000.00,'2018-09-21'),
    (2000.00,'2018-09-28'),
    (1500.00,'2018-12-25');


CREATE TABLE IF NOT EXISTS pagos 
(
    cuota_id            INT NOT NULL,
    tipo_id             INT NOT NULL,  -- por esta columna identificamos el tipo de pago: 1. Efectivo, 2. Cheque
    importe             DECIMAL(10,2),
    fecha_pago          DATETIME,
    CONSTRAINT pagos_PKA01 UNIQUE (cuota_id,fecha_pago),
    FOREIGN KEY (cuota_id) REFERENCES cuotas(cuota_id) 
        ON UPDATE CASCADE ON DELETE  CASCADE
)ENGINE=INNODB;

INSERT INTO pagos (cuota_id,tipo_id,importe,fecha_pago) 
    VALUES
    (1,1,1000.00,'2018-09-11'),
    (4,2,2000.00,'2018-09-11');

Query

SELECT 
    CASE
        WHEN p.fecha_pago IS NULL THEN 'Por pagar' 
        ELSE p.fecha_pago
     END AS status_pago,       
    CASE
        WHEN c.fecha_vencimiento < NOW() AND p.importe IS NULL THEN 'Vencido' 
        ELSE 'No-Vencido'
     END AS status_fecha,
    p.importe,
    p.tipo_id,
    c.fecha_vencimiento        
FROM cuotas c 
    LEFT JOIN pagos p ON c.cuota_id=p.cuota_id
;

Results:

status_pago     status_fecha    importe    tipo_id      fecha_vencimiento
--------------------------------------------------------------------------
2018-09-11      No-Vencido      1000,00    1                20.09.2018
2018-09-11      No-Vencido      2000,00    2                28.09.2018
Por pagar       Vencido         NULL       NULL             20.08.2018
Por pagar       No-Vencido      NULL       NULL             21.09.2018
Por pagar       No-Vencido      NULL       NULL             25.12.2018

Post-Data

I must say that there are still several pending subjects to define in your model. For example, how to determine the payments that were made, but after having exceeded the due date, that is, what payments were already due? And so, other strange situations that may arise and that should be controlled / collected.

I want to say that a data model must be done thinking even of what you have not been asked, but that you may be asked in the future. If the design is good, it will respond easily to almost everything, if it is not right, you will have to modify it to meet future requirements and sometimes a high price is paid when you have to re-structure the data already in production.