使用join构建单个SQL查询。可能吗？

问题描述：

I have 6 tables and I am going to do a single sql statement:

1)participant
  ***********
  +id_participant
  +id_poste
  +name
  +email

2) profile_formaion
  ****************
  +id_poste
  +id_formation

3) formation
  *********
  +id_formation
  +lable

4) poste
  *********
  +id_poste
  +label

5) session
  *********
  +id_session
  +id_formaion
  +lable

6) session_composition
  *********
  +id_session
  +id_participant

EXAMPLE:

DATA:participant

1 | 2 | user1 | user1@mail.com
2 | 3 | user2 | user2@mail.com

DATA:profile_formation

2 | 3
2 | 4

DATA:formation

1 |formation1
2 |formation2
3 |formation3
4 |formation4

DATA: poste

1 |Poste1
2 |Poste2
3 |Poste3

DATA:session

1 |1   /* id_session 1 to id_formation 1  and id_formation=1 is formation1 */

DATA:session_composition

1 |2  /* id_session 1 to id_participant 2 */

I am trying:

SELECT 
    participant.id_participant,
    participant.id_poste,
    participant.name,
    participant.email,
    formation.lable 
FROM participant
INNER JOIN profile_formaion ON
    profile_formaion.id_poste = participant.id_poste 
INNER JOIN formation ON
    formation.id_formation = profile_formaion.id_formation

How can I use sql statement(join) to get the result:

DATA:result

1 | 2 | user1 | user1@mail.com | poste2|formation3
1 | 2 | user1 | user1@mail.com | poste2|formation4
2 | 3 | user2 | user2@mail.com | poste3|formation1 // How can we join to get it.

我有6个表，我将做一个单独的sql语句： p> 1）参与者 *********** + id_participant + id_poste + name + email 2）profile_formaion ***** *********** + id_poste + id_formation 3）形成 ********* + id_formation + lable 4）poste ********* + id_poste + label 5）session ********* + id_session + id_formaion + lable 6）session_composition ********* + id_session + id_participant code> pre>

示例： code>

DATA：参与者 p>


  1 |  2 |  user1 |  user1@mail.com 
2 |  3 |  user2 |  user2@mail.com 
  code>  pre> 
 
  DATA：profile_formation  p> 
 
 
  2 |  3 
2 |  4 
  code>  pre> 
 
  DATA：编队 p> 
 
 
  1 | formation1 
2 | formation2 
3 | formation3 
4 | formation4  
  code>  pre> 
 
  DATA：poste  p> 
 
 
  1 | Poste1 
2 | Poste2 
3 | Poste3 
  code  >  pre> 
 
  DATA：session  p> 
 
 
  1 | 1 / * id_session 1 to id_formation 1 and id_formation = 1 is formation1 * / 
   pre> 
 
  DATA：session_composition  p> 
 
 
  1 | 2 / * id_session 1 to id_participant 2 * / 
  code>  
 
 我正在尝试： p> 
 
 
  SELECT 
 participant.id_participant，
 participant.id_poste，
 participant.name，
参与者 .email，
 formation.lable 
FROM参与者
INNER JOIN profile_formaion ON 
 profile_formaion.id_poste = participant.id_poste 
INNER JOIN组建ON 
 formation.id_formation = profile_formaion.id_formation 
  code>  pre> \  n 
 如何使用sql语句（join）获取结果： p> 
 
 
 DATA：result  p> 
 
 
  1 |  2 |  user1 |  user1@mail.com |  poste2 | formation3 
1 |  2 |  user1 |  user1@mail.com |  poste2 | formation4 
2 |  3 |  user2 |  user2@mail.com |  poste3 | formation1 //我们如何加入才能得到它。
  code>  pre> 
  div>

答

If your not averse to using unions, you can always do this:

select
    participant.id_participant,
    participant.id_poste,
    participant.name,
    participant.email,
    poste.label,
    formation.lable
from
    participant
    inner join poste on participant.id_poste = poste.id_poste
    inner join profile_formaion on participant.id_poste = profile_formaion.id_poste
    inner join formation on profile_formaion.id_formation = formation.id_formation

union all

select
    participant.id_participant,
    participant.id_poste,
    participant.name,
    participant.email,
    poste.label,
    formation.lable
from
    participant
    inner join poste on participant.id_poste = poste.id_poste
    inner join session_composition on participant.id_participant = session_composition.id_participant
    inner join session on session_composition.id_session = session.id_session
    inner join formation on session.id_formaion = formation.id_formation

使用join构建单个SQL查询。可能吗？

相关推荐