2
   select * from

(select col1,col2,col3 from tableA 
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

left join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

UNION

(select col1,col2,col3 from tableA 
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

right join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

enter image description here

7
  • Full Outer Join in MySQL Aug 28, 2017 at 13:31
  • the Problem is i need that for more than 2 tables?
    – Tarek
    Aug 28, 2017 at 13:40
  • (A LEFT JOIN B UNION B LEFT JOIN A) LEFT JOIN C UNION C LEFT JOIN (A LEFT JOIN B UNION B LEFT JOIN A). As you can see this soon gets out of hand, so you might want to use temporary tables for intermediate results. I have never needed a full outer join between more than 2 tables (thank god:-) and performance will suffer severely by doing so. Are you certain that you really need this? Aug 28, 2017 at 14:20
  • Yes i need that for the Work, they like to have all results, matched and not matched rows, all rows from each table . :-)
    – Tarek
    Aug 28, 2017 at 14:23
  • 3
    @Tarek, edit the question and add the tables' definitions and the columns that the joins will be based on. An image of tennis balls does not a good question make. Aug 28, 2017 at 14:31

2 Answers 2

1
  1. Build a VIEW that simulates a FULL OUTER JOIN of A and B. (Use the template given by some of the links.)

  2. Build a VIEW that simulates a FULL OUTER JOIN of that VIEW and C. (Use the template given by some of the links.)

  3. Etc.

Eventually, you will have a FULL OUTER JOIN of all the tables without the ugly mess of compounding the number of joins needed.

2
  • if i join the first view with Table c wich Id mus i use to Join to C (on view.col3=TableC.col3), the col3 of TableA or the col3 of TableB or is that the same thing?
    – Tarek
    Aug 29, 2017 at 14:08
  • Write out the View and the Join.
    – Rick James
    Aug 29, 2017 at 15:47
1

I know I'm coming awfully late to this, but... If this were me, given that everything appears to be joining on col3, I'd do something like:

select 
t1.*, ​t2.*, t3.*, t4.*
from
(
select col3 from t1
union select col3 from t2
union select col3 from t3
union select col3 from t4
) allcol3
left join t1 on t1.col3 = allcol3.col3
left join t2 on t2.col3 = allcol3.col3
left join t3 on t3.col3 = allcol3.col3
left join t4 on t4.col3 = allcol3.col3

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.