Vic Devic Vic Devic - 3 months ago 6
MySQL Question

ON UNION ALL, shows two search results

enter SELECT id
, stock
, nombre
, CONCAT(descuento, '%') AS descuento
, CONCAT('$', FORMAT(precio, 2)) AS precio
, CONCAT('$', FORMAT(total, 2)) AS total
, tipo
FROM producto
WHERE stock >= 1
AND visto IS NULL
AND nombre LIKE 'materna%'
UNION ALL
SELECT id
, '' AS 'stock'
, nombre
, CONCAT('$', FORMAT(descuento, 2)) AS descuento
, CONCAT('$', FORMAT(precio, 2)) AS precio
, CONCAT('$', FORMAT(total, 2)) AS total
, tipo
FROM servicio AS s
WHERE visto IS NULL
AND nombre LIKE 'materna%'


; here

picture example

It shows a result that does not correspond

Thanks

Answer

Please try using LIKE with the wildcard character %. Consider UNION instead of UNION ALL to omit duplicate rows. See live example here: SQL Fiddle.

SELECT id
     , stock
     , nombre
     , CONCAT(descuento, '%') AS descuento
     , CONCAT('$', FORMAT(precio, 2)) AS precio
     , CONCAT('$', FORMAT(total, 2)) AS total
     , tipo
  FROM producto
 WHERE stock >= 1
  AND visto IS NULL
   AND nombre LIKE 'materna%'
UNION
SELECT id
     , '' AS 'stock'
     , nombre
     , CONCAT('$', FORMAT(descuento, 2)) AS descuento
     , CONCAT('$', FORMAT(precio, 2)) AS precio
     , CONCAT('$', FORMAT(total, 2)) AS total
     , tipo 
  FROM servicio AS s 
 WHERE visto IS NULL
   AND nombre LIKE 'materna%'
;