Return values from two columns of different tables

Asked

Viewed 495 times

0

I have the STUDENTS table and the TEACHERS table, there are more teachers than students, I wanted to perform a consultation that brings students in one column and teachers in another and not all in one.

Everyone in a column was like this:

SELECT PK_PROFESSORES AS CÓDIGO_ALUNO_PROFESSOR, NOME, IF(PK_PROFESSORES != NULL, 'ALUNO', 'PROFESSOR') AS VÍNCULO
FROM PROFESSORES
UNION 
SELECT PK_ALUNO, NOME, IF(PK_ALUNO != NULL, 'PROFESSOR', 'ALUNO') AS VÍNCULO
FROM ALUNOS

There is the possibility to bring each one in a column?

mysql

  • Just out of curiosity why do you do this IF?

  • How is the relationship of your tables?

  • Because as everything returned in a column, I wanted to differentiate students from teachers, the IF is a new column that says whether it is teacher or student. hehehe

  • Teachers and students are not related.

  • And why not make a Cartesian product? Example SELECT * FROM ALUNOS, PROFESSORES

  • Returned repeated data, even using distinct.

Show 1 more comment

1 answer

2


I got it done here, see if it’s what you need

SELECT PROFESSOR.NOME, ALUNO.NOME FROM 
( 
  SELECT @rownum_p:=@rownum_p+1 AS ROW_NUM, PROFESSORES.NOME FROM PROFESSORES, (SELECT @rownum_p:=0) r
)  PROFESSOR
LEFT JOIN 
(
   SELECT @rownum_a:=@rownum_a+1 AS ROW_NUM, ALUNOS.NOME FROM ALUNOS, (SELECT @rownum_a:=0) r
) ALUNO ON PROFESSOR.ROW_NUM=ALUNO.ROW_NUM

Basically I created a "FAKE ID" and linked them.

Sqlfiddle DEMO

Browser other questions tagged mysql

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