title: Full Outer Join with multiple tables in mysql
body: <p>I am stuck in 1 query.. I want to show all products of customer as well as all sms received by system in 1 Grid/Row. I can achieve this but the thing is to display customer products only I need 3 4 other tables to join with and show all data like product model, customer name etc. this other things comes from other tables.. So I need 2 table to do outer join, and show data from 4 5 tables. I have tried but I failed.</p>
  
  <pre><code>Select      tcp.*
              , concat(tc.firstname,' ',tc.lastname)  as cust_id
              , tc.mobile
              , tb.brand_name                         as brand
              , tgt.gadget_type                       as gadget_type
              , tm.model_name                         as model
              , ttt.ticket_type                       as ticket_type
              , trs.registration_source               as registration_source 
  From        tbl_cust_products                       tcp  
  Left Join   `tbl_received_sms`                      trsm    on  tcp.id = trsm.cust_prod_id 
  Left Join   tbl_customer                            tc      on  tcp.cust_id=tc.id 
  Left Join   tbl_brand                               tb      on  tcp.brand = tb.id 
  Left Join   tbl_gadget_type                         tgt     on  tcp.gadget_type=tgt.id 
  Left Join   tbl_model                               tm      on  tcp.model = tm.id 
  Left Join   tbl_ticket_type                         ttt     on  tcp.ticket_type=ttt.id 
  Left Join   tbl_registration_source                 trs     on  trs.id=tcp.registration_source 
  Where       tcp.del_date is NULL 
  Union 
  Select      tcp.*
              , concat(tc.firstname,' ',tc.lastname)  as cust_id
              , tc.mobile
              , tb.brand_name                         as brand
              , tgt.gadget_type                       as gadget_type
              , tm.model_name                         as model
              , ttt.ticket_type                       as ticket_type
              , trs.registration_source               as registration_source 
  From        tbl_cust_products                       tcp  
  Right Join  `tbl_received_sms`                      trsm    on  tcp.id=trsm.cust_prod_id 
  Left Join   tbl_customer                            tc      on  tcp.cust_id=tc.id 
  Left Join   tbl_brand                               tb      on  tcp.brand=tb.id 
  Left Join   tbl_gadget_type                         tgt     on  tcp.gadget_type=tgt.id 
  Left Join   tbl_model                               tm      on  tcp.model = tm.id 
  Left Join   tbl_ticket_type                         ttt     on  tcp.ticket_type=ttt.id 
  Left Join   tbl_registration_source                 trs     on  trs.id=tcp.registration_source 
  Where       tcp.del_date is NULL
  </code></pre>
  
  <p>in above I want outer join only on <code>tbl_cust_products</code> and <code>tbl_received_sms</code> tables. I have tried union for <code>outer join</code> here. as I searched and find out that <code>MySql do not support direct outer join</code> like other big database handlers.</p>
  
  <p>If I am making any mistake to use union or any logic plz help me to achieve this..</p>
  
  <p><strong>EDITED</strong>
  Problem:
  In <code>tbl_received_sms</code> has 7,734 Records and in <code>tbl_cust_products</code> has 3 Records.. So I need total 7737 Records in result. If I use <code>UNION</code> only I get 3 Records, If I use <code>UNION ALL</code> I get 7737 Records but all fields of all records are <code>NULL</code>.</p>