Build single SQL query using join .is it possible?

mysql, php, sql

Solution

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

Problem

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. ```

Original source