完全外连接mysql中的多个表

Question

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.

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

in above I want outer join only on tbl_cust_products and tbl_received_sms tables. I have tried union for outer join here. as I searched and find out that MySql do not support direct outer join like other big database handlers.

If I am making any mistake to use union or any logic plz help me to achieve this..

EDITED Problem: In tbl_received_sms has 7,734 Records and in tbl_cust_products has 3 Records.. So I need total 7737 Records in result. If I use UNION only I get 3 Records, If I use UNION ALL I get 7737 Records but all fields of all records are NULL.

Answer 1

The problem is that your queries returns columns from the tables tcp (tbl_cust_products), tc (tbl_customer), tb (tbl_brand), tgt (tbl_gadget_type), tm (tbl_model), ttt (tbl_ticket_type) and trs (tbl_registration_source).

All these columns rely on a record existing on the tcp (tbl_cust_products) table, as they either come from this table or from tables that are LEFT OUTER JOINed to a record on this table.

Any row which has a matching record on tcp (tbl_cust_products) will be returned by the first query. The 2nd query will also return any of these which has a matching record on trsm (tbl_received_sms). However any that are returned by both will have one occurrence eliminated by the UNION.

The further issue is that any row that is returned from the 2nd query where there is no matching record on tcp (tbl_cust_products) will have NULL in all the fields that part of the query returns (as all the fields depend on a match on tcp (tbl_cust_products)). The UNION will then eliminate all but one of those rows, as it eliminates duplicates and all the rows are just identical (ie, all NULLs).

If you want to get output from it then add a column from trsm (tbl_received_sms) to the columns returned. Probably trsm.cust_prod_id would be a good one to try.

EDIT a bit more details to explain the unions.

Take as an example a heavily simplified version of your query:-

SELECT tcp.id,
    tc.name
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 
UNION
SELECT tcp.id,
    CONCAT(tc.firstname,' ',tc.lastname)  as cust_id
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 

Say the tables contain the following

tbl_cust_products
id  name    cust_id
1   a   5
2   b   6

tbl_received_sms
id  cust_prod_id    data
3   2       c
4   3       d
5   4       e

tbl_customer
id  name
5   fred
6   burt

The first query would return both records from tbl_cust_products, one of which is matched against tbl_received_sms:-

id  name
1   fred
2   burt

The 2nd query would find the 3 records from tbl_received_sms, one of which is matched against tbl_cust_products. The records that are unmatched have NULL in both the returned fields (as there is no matching record on tbl_cust_products the value of the field from there is null, and same for the value of the field from tbl_customer which would match the non existant record from tbl_cust_products). The record that matches will be populated:-

id      name
NULL    NULL
NULL    NULL
2       burt

The UNION will merge these 2 lots together,

id      name
1       fred
2       burt
NULL    NULL
NULL    NULL
2       burt

but eliminating the duplicates, hence:-

id      name
1       fred
2       burt
NULL    NULL