Python/Mysql- How to get the id of a table by comparing with two different

Asked

Viewed 326 times

1

I have the following two tables as shown in the following image:

inserir a descrição da imagem aqui

I wanted for example comparing the username (session level of each user) remove their id. I have used the following query but it doesn’t work SELECT id_medico FROM medico UNION SELECT id_paciente FROM paciente WHERE username=%s. The %s comes from pyhton, since I’m using the query on a page with backendin pyhton. To query complete is this: (SELECT id_medico FROM medico WHERE username=%s UNION SELECT id_paciente FROM paciente WHERE username=%s,(username,username)). So far the id returned is always the same, 1.

P.S.:I’m using the pymysql and Flask.

mysql python

  • What you want is when username for paciente1, return the id 1 of the table patients, if it’s paciente2, return the value 2, but if medico1, return the id 1 of the table doctors?

  • @Andersoncarloswoss exactly that

  • 1

    See http://sqlfiddle.com/#! 9/b6c1f/1. Thus ids are returned correctly. I used the ids 7 and 8 for doctors to make sure they are not returning wrong. The only thing is that so it is not possible to know which of the tables the id is coming. You will need to know if you are a patient or a doctor?

  • @Andersoncarloswoss this way for what I wanted is already working and I thank you. But for the future I needed to know, is it possible ?

  • Yes. It is possible. See answer.

1 answer

0


As described in the question, you already have the instruction that returns the id according to the value of username, as a subsequent:

select id_paciente as id from pacientes where username = "paciente1"
union select id_medico from medicos where username = "paciente1";

However, this way it is not possible to identify the origin of the returned value. That is, there is no way to know if the id It refers to a patient or a doctor. To do this, simply add a second static column in the selection, differing the value of the selection in the table of patients and the table of physicians:

select id_paciente as id, "paciente" from pacientes where username = "paciente1"
union select id_medico, "medico" from medicos where username = "paciente1";

See that it was added to the selection of the table patients the column "paciente", already to the selection of the table doctors column was added "medico". In this way, the result has a second column, in addition to the id, which varies according to the selected table.

Behold here the code in operation.

  • Thank you so much! Exactly what I wanted

Browser other questions tagged mysql python

You are not signed in. Login or sign up in order to post.