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).
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.
Advertisement
Answer
One method uses multiple left join
s, 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
.