Search nulls in crossing of 2 tables - MariaDB

Question

I need to get the data that I need in a specific table.

I hope you help me with this problem because I have not known how to do this.

Ligic:

Table libros
___________________________________
libro 1
libro 2
libro 3
libro 4

Table librerias
___________________________________
libreria 1 | libro 1
libreria 1 | libro 2
libreria 2 | libro 3
libreria 2 | libro 4
libreria 3 | libro 2
libreria 3 | libro 4
libreria 4 | libro 3
libreria 4 | libro 2

I want to this result
___________________________________
libreria 1 | libro 1 | libro 1
libreria 1 | libro 2 | libro 2
libreria 1 | libro 3 | NULL
libreria 1 | libro 4 | NULL
libreria 2 | libro 1 | NULL
libreria 2 | libro 2 | NULL
libreria 2 | libro 3 | libro 3
libreria 2 | libro 4 | libro 4
libreria 3 | libro 1 | NULL
libreria 3 | libro 2 | libro 2
libreria 3 | libro 3 | NULL
libreria 3 | libro 4 | libro 4
libreria 4 | libro 1 | NULL
libreria 4 | libro 2 | libro 2
libreria 4 | libro 3 | libro 3
libreria 4 | libro 4 | NULL

I have achieved it, because it cost me a little, I just thought logic and it came out.

This is the solution:



    SELECT x.libreria, x.libro, y.libro
    FROM (
        SELECT a.libreria, b.libro
        FROM librerias a
        LEFT JOIN libros b ON b.libreria=a.libreria
        GROUP BY a.libreria
    ) x
    LEFT JOIN librerias y ON y.libreria = x.libreria AND y.libro = x.libro;

Seems like you need to use an outer join. https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — clinomaniac, Nov 27 '17 at 21:56
Please write only in English and post your solution in an answer, not in the question see [tour] to learn more. — Petter Friberg, Nov 29 '17 at 19:42

score 0 · Answer 1 · answered Nov 28 '17 at 01:04

0

Something like this:

SELECT a.liberia, b.libros, c.libros
    FROM ( SELECT DISTINCT liberia FROM liberias ) AS a
    JOIN libros AS b
    LEFT JOIN liberia AS c  ON c.libros = b.libros
                           AND c.liberia = b.liberia

answered Nov 28 '17 at 01:04

Rick James

135,179
13
127
222

Search nulls in crossing of 2 tables - MariaDB

1 Answers1