Can't process MySQL FULL JOIN query

Question

I want to FULL JOIN following two tables

Table 1: Persons

P_Id      Name            
 1         A        
 2         B       
 3         C
 4         D
 5         E

Table 2: Invoice

  Id       P_Id            
 111        3        
 112        3       
 113        1
 114        1
 115       15

I used this query :

SELECT Persons.Name, Persons.P_Id, Invoice.Id
FROM Persons
FULL JOIN Invoice
ON Persons.P_Id=Invoice.P_Id
ORDER BY Persons.Name

But this generates an error

Unknown column 'Persons.Name' in 'Field list'

MySQL server version is 5.5.19 and I used command line client in Microsoft Windows7

INNER JOIN, RIGHT JOIN and LEFT JOIN worked for me but I can't perform FULL JOIN. What is the error please tell me I'm still a student. How can I perform FULL JOIN here ?

Thank You!

Solved by using UNION of LEFT JOIN and the RIGHT JOIN of both tables Thank you! — Grant
– Grant, Commented Nov 28, 2012 at 6:08

John Woo · Accepted Answer · 2012-11-28 05:22:01Z

5

in order to simulate FULL JOIN in mysql, you need to UNION the result of Right Join and Left Join from both tables

SELECT  Persons.Name, Persons.P_Id, Invoice.Id
FROM    Persons
        LEFT JOIN Invoice
            ON Persons.P_Id=Invoice.P_Id
UNION
SELECT  Persons.Name, Persons.P_Id, Invoice.Id
FROM    Invoice
        LEFT JOIN Persons
            ON Persons.P_Id=Invoice.P_Id

SQLFiddle Demo

edited Nov 28, 2012 at 5:22

answered Nov 28, 2012 at 5:15

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

John Woo Over a year ago

@LolCoder nope, i used to use all LEFT join rather the left and right join. see the interchanging of columns. :D

Collectives™ on Stack Overflow

Can't process MySQL FULL JOIN query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related