Mysql alias in all Columns

Asked

Viewed 562 times

2

Good afternoon Galera.

I have a little problem to join some tables by INNER JOIN, and because the columns name are equal, I imagine that the ALIAS can save me by changing the columns name pro front.

Turns out, I’ll have to do this on several pages and many different columns, I can use * SELECT and ask to use Alias in all columns?

Something like SELECT * AS tabelarenomeada_* FROM tabela to not have to rename column by column? For one of the querys I have as an example is like this:

SELECT turmas.id AS turma_id, turmas.id_professor AS 
turma_idprofessor, turmas.id_curso AS turma_idcurso, 
turmas.data_inicio, turmas.qtd_alunos,
professores.id AS professor_id, professores.nome_professor AS 
professor_nome,
cursos.id AS curso_id
FROM turmas
INNER JOIN professores ON turmas.id_professor = professores.id 
INNER JOIN cursos ON turmas.id_curso = cursos.id 
WHERE turmas.ativo = 1 AND professores.ativo = 1 AND cursos.ativo = 1

Is there any way to force all columns to use ALIAS with a fixed name in front of the original name for example?

mysql mariadb alias

2 answers

4


Hello Rafael you can define an alias for the tables. Using your example, would do as follows:

SELECT TUR.id AS turma_id,
       TUR.id_professor AS turma_idprofessor,
       TUR.id_curso AS turma_idcurso, 
       TUR.data_inicio,
       TUR.turmas.qtd_alunos,
       PROF.id AS professor_id,
       PROF.nome_professor AS professor_nome,
       CUR.id AS curso_id
   FROM turmas TUR
     INNER JOIN professores PROF ON TUR.id_professor = PROF.id 
     INNER JOIN cursos CUR ON TUR.id_curso = CUR.id 
  WHERE TUR.ativo = 1 AND PROF.ativo = 1 AND CUR.ativo = 1

Remember that a good identation and standardization of the name of the tables and columns facilitates the maintenance and interpretation of the code.

  • Oh yes, I understood the alias on the chart, but I think I misexpressed myself. What I would like is to be able to use * in the search but somehow rename the column. In this case I will use a lot SELECT * FROM tabela1 INNER JOIN tabela2 for example. I wish I could continue using * without having to rename column by column. Is that possible? (Ah and sorry for the misconception, when copying the code came badly formatted)

  • 1

    you could define so SELECT * FROM turmas tur INNER JOIN professores PROF ON TUR.id_professor = PROF.id INNER JOIN cursos CUR ON TUR.id_curso = CUR.id

  • 1

    It is possible yes Rafael vc use the * in an Inner Join... When you set select coluna1, coluna2 from exemplo vc restricts the search only to the informed columns... when you use *, all columns of the junction between Table 1 and table2 will be returned.

  • Got it, so would be perfect my query, but in this case, when I bring result to Frontend the column name will still be the original no? Because my problem is when I will display this in an HTML table when I use id for example, it can be ambiguous, since I didn’t rename it to TUR.id as an example.

  • 1

    In this case yes, the name would be equal to the column name. Only the way to pass * would not be the most correct one. For example: here in the company we use Protheus, your BD has table that has more than 100 columns and the table names are similar to this pattern SB1010, SB5010... Just imagine making a product query bringing all the tables of SB1010 together with your product complement SB5010... It would allocate a lot of resource on the machine besides bringing unnecessary data to each query...

  • 1

    With each query, we define the columns and their alias... It takes a little more work but makes it easier to maintain the code and it is also possible to use masks in the column alias in this way: select B1_COD as [Código], B1_DESC as [Descrição] from SB1010

  • This is true. It could turn more headache than solution. Thank you very much for the help and the tips Leonardo. Helped me a lot and clarified a lot too. A hug =]

Show 2 more comments

1

The biggest problem I see in building select with *:

When you have 1 table without the use of iiner Join, the use of * is valid. However, see that the Inner Join is to include the fields of the teacher table and course in the class table. But if you do not put these fields in select, they will not appear in the query.

In select you have to specify which fields of the table class, teacher and course you want to select and see after making the inclusion by Inner Join.

One way to do it is as mentioned by incidentally in the table name also, this way:

SELECT TUR.*,
       PROF.id AS professor_id,
       PROF.nome_professor AS professor_nome,
       CUR.id AS curso_id
   FROM turmas TUR
     INNER JOIN professores PROF ON TUR.id_professor = PROF.id 
     INNER JOIN cursos CUR ON TUR.id_curso = CUR.id 
  WHERE TUR.ativo = 1 AND PROF.ativo = 1 AND CUR.ativo = 1

Thus selects all fields of the class table and more the specifics of the Internet teacher and the course.

Browser other questions tagged mysql mariadb alias

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