MYSQL query does not result

Question

MYSQL query does not result

Navigation

#1 by (2 votes)

2

I have the following mysql query.

SELECT (CASE WHEN s.id='1' THEN r.nombre ELSE 0 END) as sucursal1,
(CASE WHEN s.id='2' THEN r.nombre ELSE 0 END) as sucursal2 
FROM repuestos r INNER JOIN sucursales s on s.id = r.idsucursal WHERE r.codigo_venta=1992

and the result is.

adding group by r.codigo_venta gives me results.

The result I want to obtain is:

I need guidance.

mysql

asked by JohnL 06.08.2018 в 01:05

source

1 answer

Change the width of the bars in google chart Failed to display data list in Java

score 2 · Accepted Answer

The problem is that actually every id is a different row, that's why it throws you two rows. One way to solve this would be through a GROUP_CONCAT for each branch, combined with a GROUP BY r.codigo_venta , something like this:

SELECT 
    r.codigo_venta,
    GROUP_CONCAT(CASE WHEN s.id=1 THEN r.nombre ELSE NULL END SEPARATOR '') sucursal1,
    GROUP_CONCAT(CASE WHEN s.id=2 THEN r.nombre ELSE NULL END SEPARATOR '') sucursal2      
FROM repuestos r 
    INNER JOIN sucursales s on s.id = r.idsucursal 
    WHERE r.codigo_venta=1992
    GROUP BY r.codigo_venta ;

Proof of concept

Here is a test based on real data, you can SEE A DEMONSTRATION IN REXTESTER

CREATE TABLE IF NOT EXISTS sucursales 
(
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sucursal      VARCHAR(150),
    CONSTRAINT sucursal_PKA01 UNIQUE (sucursal)
 )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS repuestos 
(
    id             INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nombre         VARCHAR(50),
    idsucursal     INT,
    codigo_venta   INT,
    CONSTRAINT repuestos_PKA01 UNIQUE (nombre),
    FOREIGN KEY (idsucursal) REFERENCES sucursales(id) 
        ON UPDATE CASCADE ON DELETE  CASCADE
)ENGINE=INNODB;

INSERT INTO sucursales (sucursal) 
    VALUES
    ('Sucursal 1'),
    ('Sucursal 2'),
    ('Sucursal 3');

INSERT INTO repuestos (nombre, idsucursal, codigo_venta) 
    VALUES
    ('Bujía 1', 1, 1992),
    ('Bujía 2', 2, 1992),
    ('Bujía 3', 1, 1993),
    ('Bujía 4', 1, 1994) ;

SET sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT 
    r.codigo_venta,
    GROUP_CONCAT(CASE WHEN s.id=1 THEN r.nombre ELSE NULL END SEPARATOR '') sucursal1,
    GROUP_CONCAT(CASE WHEN s.id=2 THEN r.nombre ELSE NULL END SEPARATOR '') sucursal2      
FROM repuestos r 
    INNER JOIN sucursales s on s.id = r.idsucursal 
    WHERE r.codigo_venta=1992
    GROUP BY r.codigo_venta ;

Result:

codigo_venta    sucursal1      sucursal2
---------------------------------------------
1992            Bujía 1        Bujía 2