merge two sql tables

Question

I have two SQL tables that are not related to each other. But I need these two to show me the name.

The queries are:

SELECT ca_nombre 
FROM cancion 
ORDER BY ca_nombre ASC;

SELECT bai_nombre 
FROM baile 
ORDER BY bai_nombre ASC;

The output:

Amiga
Gracias a ti
La gata bajo la lluvia
Las locuras mías
Tu
Break dance
Danza clásica
Danza irlandesa
Flamenco
Polca
Salsa
cumbia
samba

but I need the output to be like this:

Amiga
Arrow
Break dance
Cumbia
Danza clasica
Danza irlandesa
Gracias a ti
La gata bajo la lluvia
Las locuras mias
Polca
Salsa
Samba
Tu

The cancion table has this data:

Amiga
Gracias a ti
La gata bajo la lluvia
Las locuras mías
Tu

and the dance table has this data:

Break dance
    Danza clásica
    Danza irlandesa
    Flamenco
    Polca
    Salsa
    cumbia
    samba

How do I combine them and get the output as I need it?

This is the cancion and baile table scripts:

CREATE TABLE baile
(
    bai_id INT NOT NULL,
    bai_nombre CHAR(30) NOT NULL,
    bai_antiguedad INT NULL,
    bai_duracion INT NULL,

    FOREIGN KEY (bai_id) REFERENCES concurso(con_id)
);

INSERT INTO baile VALUES (102, "cumbia", 40, 5);
INSERT INTO baile VALUES (102, "samba", 50, 5);
INSERT INTO baile VALUES (102, "Break dance", 30, 4);
INSERT INTO baile VALUES (102, "Salsa", 30, 4);
INSERT INTO baile VALUES (102, "Danza irlandesa", 34, 7);
INSERT INTO baile VALUES (102, "Flamenco", 35, 6);
INSERT INTO baile VALUES (102, "Polca", 50, 6);
INSERT INTO baile VALUES (102, "Danza clásica", 56, 5);

CREATE TABLE cancion
(
    ca_id INT NOT NULL AUTO_INCREMENT,
    ca_nombre CHAR(30) NULL,
    ca_genero CHAR(20) NULL,
    ca_anio YEAR NULL,
    ca_cantante INT NOT NULL,

    FOREIGN KEY (ca_id)REFERENCES concurso (con_id),
    FOREIGN KEY (ca_cantante) REFERENCES cantante (can_id)
);

INSERT INTO cancion VALUES (102, "Amiga", "Balada", "1990", 101);
INSERT INTO cancion VALUES (102, "Las locuras mías", "vallenato", "2019", 102);
INSERT INTO cancion VALUES (102, "Gracias a ti", "popular", "2020", 103);
INSERT INTO cancion VALUES (102, "Tu", "popular", "2019", 104);
INSERT INTO cancion VALUES (102, "La gata bajo la lluvia", "Balada", "1981", 105);

Although it has foreign keys they are not necessary for the query, since I only need the name of the songs and dances in alphabetical order.

score 1 · Accepted Answer · answered Aug 28 '21 at 23:24

It looks like you want the data sorted overall. You haven't specified your database, but this would work in most databases:

SELECT ca_nombre as nombre
FROM cancion 
UNION ALL
SELECT bai_nombre
FROM baile
ORDER BY nombre ASC;

Some databases, though, require the UNION ALL to be in a subquery:

SELECT nombre
FROM (SELECT ca_nombre as nombre
      FROM cancion 
      UNION ALL
      SELECT bai_nombre
      FROM baile
     ) n
ORDER BY nombre;

merge two sql tables

1 Answers1