Skip to content
Advertisement

Multiple full joins in one query on multiple tables

I have a database with multiple tables, I wish to show all contents from every table with one query (that I will use later for a research in the database).

view of the database

I’ve tried with two tables

[SELECT 
    reception.num_courrier, reception.date_recep, reception.expediteur, reception.objet, 
    lect_prefet.date_lect_prefet, lect_prefet.instructions, lect_prefet.caractere 
FROM
    reception 
FULL JOIN 
    lect_prefet ON reception.num_courrier = lect_prefet.num_courrier][2]

but it returns an error.

I’m using 10.4.14-MariaDB version.

full join query error

Advertisement

Answer

One method uses multiple left joins, starting with all available ids:

SELECT *
FROM (SELECT r.num_courrier
      FROM reception r
      UNION -- on purpose to remove duplicate 
      SELECT l.num_courrier 
      FROM lect_prefet l
     ) c LEFT JOIN
     reception r
     USING (num_courrier) LEFT JOIN
     lect_prefet l
     USING (num_courrier);

This approach easily generalizes to more tables using the same key for the JOIN.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement