MYSQL - mirlz/snippets GitHub Wiki

Multiquery

Joining 3 tables

postal - entries created by person (contains information on who created, details of postal etc) person_information - to get person's information (birthday, mobile, email, etc) file_company - person's company (a person can have more than 1 company)

Objective To set country_id in postal table, this is done by getting user_id from postal, join to person_information to get person's company, which is used to join with file_company to get the country of the company.

UPDATE postal p
JOIN
hr_personal_information h ON (p.created_by = h.id
	AND h.id = (
	SELECT 
	 id
	FROM
	 hr_personal_information
	WHERE
	 id = h.id
))
JOIN 
code_file_legal_entity c ON(h.legal_entity_id_cache = c.id)
SET p.country_id = c.country_id
WHERE
 h.legal_entity_id_cache IS NOT NULL;