Menu
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 | [email protected]
2 | 3 | user2 | [email protected]
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 | [email protected] | poste2|formation3
1 | 2 | user1 | [email protected] | poste2|formation4
2 | 3 | user2 | [email protected] | poste3|formation1 // How can we join to get it.
592
While most JOINs link two or more tables with each other to present their data together, a self join links a table to itself. This is usually done by joining a table to itself just once within a SQL query, but it is possible to do so multiple times within the same query.
Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables.
(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
An SQL query can JOIN multiple tables. For each new table an extra JOIN condition is added. Multi-Table JOINs work with SELECT, UPDATE, and DELETE queries.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
