Join two different lists in MYSQL sorted by date?

Question

Hello community I am working with MySql database, I have two lists with different records, but I want them together one below the other, depending on the date origin as well as I show below:

The 1st list:

SELECT  p.ICODPRESTAMO, 
    p.NCAPITAL, 
    (p.NMONPRESTAMO - p.NCAPITAL) AS INTERES, 
    DATE_FORMAT(p.TSFECCREACION,'%d/%m/%Y') AS FECHA
FROM TABPRESTAMO p
WHERE  p.ICODPADPRESTAMO IS NOT NULL 
    AND p.ICODPERBENEFICIARIA = 2
    AND p.SICODESTADO = 1;

SQL Return:

ICODPRESTAMO    NCAPITAL    INTERES FECHA
2   2000.00 200.00  01/02/2014
3   1650.00 247.50  04/06/2014
4   6000.00 900.00  09/06/2014
5   60.00   0.00    22/07/2014
6   600.00  48.00   06/01/2014
7   1500.00 112.50  28/03/2014
8   1600.00 64.00   24/06/2014
9   525.00  0.00    13/09/2014
18  582.00  0.00    03/10/2015

The 2nd list:

SELECT  a.ICODAMORTIZACION,
          a.NABOMONTO,
          DATE_FORMAT(a.TSFECCREACION,'%d/%m/%Y') AS FECHA
FROM TABAMORTIZACION a
WHERE  a.ICODPRESTAMO = 1
    AND a.SICODESTADO = 1;

SQL Return:

ICODAMORTIZACION    NABOMONTO   FECHA
1   200.00  16/03/2014
2   200.00  23/03/2014
3   200.00  30/03/2014
4   200.00  13/04/2014
5   200.00  20/04/2014
6   150.00  27/04/2014
7   150.00  04/05/2014
8   200.00  10/05/2014
9   200.00  17/05/2014
10  150.00  24/05/2014
11  232.00  15/09/2014

My goal is to unite the registration of each other, depending on the FECHA

As the image below:

https://i.stack.imgur.com/JsH5F.jpg

Develop my procedure as follows:

DELIMITER //
CREATE PROCEDURE reporte4(cod_prestamo INT, cod_per_beneficiaria INT)
BEGIN

    -- Declaración de variables
    DECLARE ID_PRESTAMO INT;
    DECLARE CAPITAL DECIMAL(8,2);
    DECLARE PRESTAMO DECIMAL(8,2);
    DECLARE FECHA VARCHAR(10);

    -- Definición de la consulta
    DECLARE prestamo_beneficiario CURSOR FOR
    SELECT  p.ICODPRESTAMO, 
        p.NCAPITAL, 
        (p.NMONPRESTAMO - p.NCAPITAL) AS INTERES, 
        DATE_FORMAT(p.TSFECCREACION,'%d/%m/%Y') AS FECHA
    FROM TABPRESTAMO p
    WHERE  p.ICODPADPRESTAMO IS NOT NULL 
         AND p.ICODPERBENEFICIARIA = 2
         AND p.SICODESTADO = 1;

    -- Declaración de un manejador de error tipo NOT FOUND
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;

    -- Abrimos el cursor
    OPEN prestamo_beneficiario;

    -- Comenzamos nuestro bucle de lectura
    loop1: LOOP

    -- Obtenemos la primera fila en la variables correspondientes
    FETCH prestamo_beneficiario INTO ID_PRESTAMO, CAPITAL, PRESTAMO, FECHA;

    -- Si el cursor se quedó sin elementos,
    -- entonces nos salimos del bucle
    IF @hecho THEN
    LEAVE loop1;
    END IF;

    -- Guardamos el acumulado de ventas y el código
    -- si el vendedor actual tiene mejores resultados
    -- SET FECHA_EVA = FECHA_EVA + FECHA;

    -- SELECT FECHA_EVA;

    END LOOP loop1;

    -- Cerramos el cursor
    CLOSE prestamo_beneficiario;

   SELECT prestamo_beneficiario;

END //
DELIMITER ;

I hope you can advise me how I should handle this case.

show your desired results also in your post based on 1st 2 results. — Zafar Malik, Oct 07 '15 at 06:42

score 0 · Answer 1 · answered Oct 07 '15 at 06:49

0

In MySQL use UNION method

for example

SELECT
    `column_id` AS `id`,
    `column_name` AS `name`,
    `column_date` AS `date`
FROM `table_name`

UNION
SELECT
    `column_id` AS `id`,
    `column_name` AS `name`,
    `column_date` AS `date`
FROM `second_table_name`

ORDER BY `date`;

answered Oct 07 '15 at 06:49

Siddhartha esunuri

1,104
1
17
29

That will not work properly when he has the same date in both SELECT statements. – Mihai Ovidiu Drăgoi Oct 07 '15 at 06:53

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

I found the question a little unclear, but i think what you want is a FULL JOIN.

Basically, you need to do:

select * from
(
  select 1
) a
FULL JOIN
(
  select 2
) b
on a.date = b.date

where select 1 and select 2 are what you already posted.

But, since MySQL doesn't support FULL JOIN, you'll need to emulate it, the way to do it is explained here

edited May 23 '17 at 12:17

Community

1
1

answered Oct 07 '15 at 06:51

Mihai Ovidiu Drăgoi

1,307
1
10
16

score 0 · Accepted Answer · answered Oct 07 '15 at 07:16

Then use ASC order

SELECT
    `column_id` AS `id`,
    `column_name` AS `name`,
    `column_date` AS `date`
FROM `table_name`

UNION
SELECT
    `column_id` AS `id`,
    `column_name` AS `name`,
    `column_date` AS `date`
FROM `second_table_name`

ORDER BY `date` ASC;

Join two different lists in MYSQL sorted by date?

3 Answers3