Relate 3 tables in one row - MYSQL

Question

Relate 3 tables in one row - MYSQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

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

Image of the table

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_p contact_e contact_t contact_w contact_m

table: clients_documents

id user_id file_name attachment date

I initially thought about using a UNION getting 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 circumvent this problem?

mysql sql

asked by anonymous 12.05.2017 / 04:12

2 answers

0

Hello

The problem of using the "Union" command in this case is that it requires the same number of columns in the join of the tables.

Suppose you have the following scheme:

table A (colA, ColB, ColC)

table B (colA, ColD, ColE)

To make Union of the two tables, it is necessary that they 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 "cleaner". Good luck!

12.05.2017 / 05:23

Normalize encoding of an entire solution Character removal on Heroku server

score 0 · Accepted Answer

UNION serves to join together similar data from different queries.

To relate tables to each other from a primary / foreign key you need 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