MySQL a query to get user_id and password from two different tables-CodePudding

Professeur (professeur_id, name , password,mail)
Student (student_id, name, password, mail)

How can I write a query to get ID and password from the two tables?

Also, can I make a login table that contains ID and password as a foreign key from the two tables?

(Do I have to change the professeur ID and student ID columns to have the same name?)

CodePudding user response：

Look here about "join": https://www.cloudways.com/blog/how-to-join-two-tables-mysql/

CodePudding user response：

Use a union select:

select
  p.professeur_id as id
  , p.name as name
  , p.password as password
  , p.mail as mail
  from Professeur p
union select
  s.student_id as id
  , s.name as name
  , s.password as password
  , s.mail as mail
  from Student s
  ;

Please note that at least the fields professeur_id and student_id must have an alias because of their different name. But giving all fields an alias is a good practice.

For more information (not only on select statement) please go to official MySQL documentation