perform query with a condition in mysql

Question

perform query with a condition in mysql

Navigation

#1 by (2 votes)
#2 by (2 votes)

2

I have a person table and a session and in the session table I have the category column, so I want make a query that ignores people who are admin I have tried these codes and I have not obtained the result I want.

create procedure listarUsuarios()
begin
select 
persona.nombres as nombres,
persona.apellidos as apellidos,
persona.correo as correo,
persona.codigo as codigo,
sesion.categoria as categoria
from persona, sesion
where persona.correo = sesion.correo and sesion.categoria not like 'admin%';
end
//

mysql sql select stored-procedure

asked by Leonard Jusa Ockonell 12.03.2018 в 02:53

source

2 answers

2

This code returns all the people who are not admin:

select 
persona.nombres as nombres,
persona.apellidos as apellidos,
persona.correo as correo,
persona.codigo as codigo,
sesion.categoria as categoria
from persona, sesion
where persona.correo = sesion.correo and sesion.categoria != "admin";

answered by 12.03.2018 в 03:13

ASP.NET Calculator Problem when listing files in a directory, the first result shows blank

score 2 · Accepted Answer

aside from the fact that you want to create a stored procedure I commented that the best thing for multi-table queries is to use JOINS, since they simplify the workload at the time of the consultations; in the following lines you will notice that instead of passing the value of the field as text string is "admin", use the id that identifies the category of admins

SELECT usuarios.*, categorias.* FROM usuarios
JOIN categorias
ON  usuarios.id_categoria = categorias.id
WHERE usuarios.id_categoria = 1;

Now if you want all the users that are not admins, just pass the id that identifies that category or role or as you have named it in your table

In my example, id 1 is for admins and id 2 is for guests, it's just a matter of changing those values

// UPDATE

In order to create your AP, follow my next example and then send it to call

DELIMITER $$
CREATE PROCEDURE datos()
BEGIN
   SELECT usuarios.*, categorias.* FROM usuarios
JOIN categorias
ON  usuarios.id_categoria = categorias.id
WHERE usuarios.id_categoria = 1;
END $$


call datos();

As you can see if you only do the PA it will not throw anything, but if you execute call followed by the PA name it will show you its functionality

At the end if you want to know or remember the names of the PAs that you created, just execute the following command Important do not alter the ones you did not create

SHOW PROCEDURE STATUS;