List 3 tables in a row - MYSQL

Question

I want to make a table together information from 3 tables. This table will be ordered by date (day/month/year Hours:Minutes) and visually speaking would look like this

Table image

For better understanding I will post the structure of the banks

table: realation_notes

id user_id comments comment_date

table: historic_attendance

id user_id date contact_contact

table: clients_documents

id user_id file_name Attachment date

initially thought to use a UNION more or less like this

(SELECT id, user_id, comments, comment_date AS date FROM `realation_notes` 
                                                   WHERE `user_id` = 582)
UNION
(SELECT * FROM `historic_attendance` WHERE `user_id` = 582)
UNION
(SELECT * FROM `clients_documents` WHERE `user_id` = 582)
ORDER BY date

but this SQL returns me the following error:

Os comandos SELECT usados têm diferente número de colunas

how could I get around this problem?

Answer 1

Hello!

The problem with using the "Union" command in this case is that it requires the same number of columns in the table union.

Imagine you have the following scheme:

table A (glue, Colb, Colc)

table B (glue, Cold, Cole)

To make Union of the two tables you need them to have the same fields. Soon,

select cola, Colb, Colc, null, null from table A Union select cola, null, null, Cold, Cole from table B

You can, if you want, use an alias in the Null values to make the data more "clean". Good luck!

Answer 2

UNION serves to join similar data set coming from different queries.

To relate tables to each other from a primary/foreign key it is necessary to use some form of JOIN type:

SELECT r.id, r.user_id, r.comments, r.comment_date, h.*, d.*
FROM realation_notes AS r
JOIN historic_attendance AS h ON r.user_id = h.user_id
JOIN clients_documents AS d ON r.user_id = d.user_id
WHERE r.user_id = 582