2

when i try to join tables with inner join..it returns the data..but when i join 4 tables with full outer join then i says

ERROR: FULL JOIN is only supported with merge-joinable join conditions"

An example query:

SELECT hr_employee.name,hr_movement.amount,hr_concept.name,hr_period.name
FROM hr_employee
FULL OUTER JOIN hr_movement
ON hr_employee.ad_client_id=hr_movement.ad_client_id
FULL OUTER JOIN hr_concept
ON hr_movement.ad_client_id=hr_employee.ad_client_id
FULL OUTER JOIN hr_period
ON hr_concept.ad_client_id=hr_employee.ad_client_id

2 Answers 2

1
SELECT hr_employee.name,hr_movement.amount,hr_concept.name,hr_period.name
FROM hr_employee
FULL OUTER JOIN hr_movement
ON hr_employee.ad_client_id=hr_movement.ad_client_id
FULL OUTER JOIN hr_concept
ON hr_movement.ad_client_id=hr_employee.ad_client_id /*<- here*/
FULL OUTER JOIN hr_period
ON hr_concept.ad_client_id=hr_employee.ad_client_id /*<- and here*/

You're joining tables, but don't use these tables in the join condition. Apart from that MySQL doesn't support FULL OUTER JOINs. They are realized like in this answer.

Sign up to request clarification or add additional context in comments.

Comments

0

There is no FULL OUTER JOIN in MySQL. See 7.2.12. Outer Join Simplification and 12.2.8.1. JOIN Syntax:

You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):

with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

with three tables t1, t2, t3:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

Source

Take a look at here

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.