A field in table 1 related to 2 fields in table 2

Asked

Viewed 41 times

2

Let us suppose two tables

Table 1 : emp (table of employees)

Campos : emp.no (chave privaria), emp.name (chave múltipla, não primaria)

Table 2: webConference 

Campos: webConferencia.no (chave primaria), webConferencia.conferenteno
(não primária, smallint), webConferencia.embaladorno (não primária, smallint)

I want to get the name of the employee (emp.name), being them conferentes (webConferencia.conferenteno) and packer (webConferencia.embaladorno), how to relate 1 primary key field of a table with 2 fields of a second table simultaneously?

webConferencia.conferenteno = emp.no
webConferencia.embaladorno  = emp.no
mysql sql query
  • 1

    I was able to pull together two temporary selects. First I made the left Join with the conference field and then in the second season I made the left Join with the packed field

  • 1

    Hello @Tiago Damasio if you were able to solve it, add your solution code or an example that you find useful to share with the OS.

1 answer

1

If in the table webConference will always have the reference of an employee you can do only the JOIN.

In cases where two columns refer to the primary key of another table can do the Query giving a different "nickname" to the same table when it comes to the other column, see the example:

SELECT webConferencia.no, conferente.name, embalador.name
FROM webConferencia
JOIN emp conferente ON webConferencia.conferenteno = conferente.no /* emp apelidada de conferente */
JOIN emp embalador ON webConferencia.embaladorno = embalador.no /* emp apelidada de embalador */

In this example above, you can notice that there are two columns name, if you want to identify them just put a "nickname" in them as well as in the tables:

SELECT webConferencia.no, conferente.name conferente /* apelidada de conferente */, embalador.name embalador /* apelidada de embalador */
FROM webConferencia
JOIN emp conferente ON webConferencia.conferenteno = conferente.no
JOIN emp embalador ON webConferencia.embaladorno = embalador.no

Browser other questions tagged mysql sql query

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