How to use a Case statement in Mysql?

Question

How to use a Case statement in Mysql?

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I have two tables one is the detail of product and the other is the product, the problem lies in that I want to make a left join to bring me the data that are related in both tables, the attribute that must be related is the "Number of Product", that should be the same in both tables, but apart I want to bring the rest of the records only when the relationship is the same send me if there is a relationship and when not only mark me there is no relationship in a separate attribute, but I do not with inner join because there will only bring me those that match and what I require is that you bring all but mark me if there is a relationship or not in each record of the table.

Here is the code of my current query

 SELECT
    ct.sNumeroProducto as Producto, detalle.sProducto as Detalle
    case ct.sNumeroProducto  when ct.sNumeroProducto=detalle.sProducto then 'Todos'  when ct.sNumeroProducto!=detalle.sProducto then 'En vigor'
    FROM ct_producto ct  left join  ct_producto_detalle detalle on detalle.sProducto=ct.sNumeroProducto

The error is as follows

11:54:08 Kernel error: Error( 1064 ) 42000: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case ct.sNumeroProducto  when ct.sNumeroProducto=detalle.sProducto then 'Todos' ' at line 3"

Note: I do not want to store it permanently in the DB, but in a virtual column only show it

mysql sql

asked by David 19.10.2016 в 19:00

source

2 answers

0

You had a poorly structured case sentence.

Try the following code:

    SELECT
        ct.sNumeroProducto as Producto, 
        detalle.sProducto as Detalle
        case  
           when ct.sNumeroProducto=detalle.sProducto then 'Todos'  
           when ct.sNumeroProducto!=detalle.sProducto then 'En vigor'
        end case
    FROM ct_producto ct  
         left join  ct_producto_detalle detalle 
         on detalle.sProducto = ct.sNumeroProducto

according to the documentation of MySql

answered by 19.10.2016 в 19:11

Absolute URLs with PHP how to parse angularjs data

score 3 · Accepted Answer

You had an error declaring the query, as well as a missing comma.

SELECT 
    ct.sNumeroProducto AS Producto,
    detalle.sProducto AS Detalle,
    CASE
        WHEN ct.sNumeroProducto = detalle.sProducto THEN 'Todos'
        WHEN ct.sNumeroProducto != detalle.sProducto THEN 'En vigor'
    END as Estado
FROM
    ct_producto ct
        LEFT JOIN
    ct_producto_detalle detalle ON (detalle.sProducto = ct.sNumeroProducto)

Greetings