CODEWITHSUNDEEP
mysqlsqldatabase
JPC
JPC

Reputation: 5173

How to join tables in mysql with different column

These are my tables:

If I use table 2 left join table 1 then I only have from 3 - 7 .. what kind of join do I need in this case ?

Thank you

Upvotes: 2

Views: 112

Answers (2)

John Woo
John Woo

Reputation: 263733

Unfortunately, MySQL doesn't support FULL OUTER JOIN. But still, you can emulate it.

SELECT  a.ID1,  
        b.ID2, 
        b.var1, 
        b.var2
FROM    TableA a
        LEFT JOIN TableB b
            ON a.ID2 = b.ID1
UNION 
SELECT  COALESCE(b.ID1, a.ID2), 
        a.ID2, 
        a.var1, 
        a.var2
FROM    Tableb a
        LEFT JOIN TableA b
            ON b.ID2 = a.ID1

Upvotes: 3

dugas
dugas

Reputation: 12453

You need a full outer join. See this article by Xaprb for emulating in MySql:

http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

Upvotes: 2

Related Questions