php - Full Outer Join with multiple tables in mysql -
i stuck in 1 query.. want show products of client sms received scheme in 1 grid/row. can accomplish thing display client products need 3 4 other tables bring together , show info product model, client name etc. other things comes other tables.. need 2 table outer join, , show info 4 5 tables. have tried failed.
select tcp.* , concat(tc.firstname,' ',tc.lastname) cust_id , tc.mobile , tb.brand_name brand , tgt.gadget_type gadget_type , tm.model_name model , ttt.ticket_type ticket_type , trs.registration_source registration_source tbl_cust_products tcp left bring together `tbl_received_sms` trsm on tcp.id = trsm.cust_prod_id left bring together tbl_customer tc on tcp.cust_id=tc.id left bring together tbl_brand tb on tcp.brand = tb.id left bring together tbl_gadget_type tgt on tcp.gadget_type=tgt.id left bring together tbl_model tm on tcp.model = tm.id left bring together tbl_ticket_type ttt on tcp.ticket_type=ttt.id left bring together tbl_registration_source trs on trs.id=tcp.registration_source tcp.del_date null union select tcp.* , concat(tc.firstname,' ',tc.lastname) cust_id , tc.mobile , tb.brand_name brand , tgt.gadget_type gadget_type , tm.model_name model , ttt.ticket_type ticket_type , trs.registration_source registration_source tbl_cust_products tcp right bring together `tbl_received_sms` trsm on tcp.id=trsm.cust_prod_id left bring together tbl_customer tc on tcp.cust_id=tc.id left bring together tbl_brand tb on tcp.brand=tb.id left bring together tbl_gadget_type tgt on tcp.gadget_type=tgt.id left bring together tbl_model tm on tcp.model = tm.id left bring together tbl_ticket_type ttt on tcp.ticket_type=ttt.id left bring together tbl_registration_source trs on trs.id=tcp.registration_source tcp.del_date null
in above want outer bring together on tbl_cust_products
, tbl_received_sms
tables. have tried union outer join
here. searched , find out mysql not back upwards direct outer join
other big database handlers.
if making error utilize union or logic plz help me accomplish this..
edited problem: in tbl_received_sms
has 7,734 records , in tbl_cust_products
has 3 records.. need total 7737 records in result. if utilize union
3 records, if utilize union all
7737 records fields of records null
.
the problem queries returns columns tables tcp (tbl_cust_products), tc (tbl_customer), tb (tbl_brand), tgt (tbl_gadget_type), tm (tbl_model), ttt (tbl_ticket_type) , trs (tbl_registration_source).
all these columns rely on record existing on tcp (tbl_cust_products) table, either come table or tables left outer joined record on table.
any row has matching record on tcp (tbl_cust_products) returned first query. 2nd query homecoming of these has matching record on trsm (tbl_received_sms). returned both have 1 occurrence eliminated union.
the farther issue row returned 2nd query there no matching record on tcp (tbl_cust_products) have null in fields part of query returns (as fields depend on match on tcp (tbl_cust_products)). union eliminate 1 of rows, eliminates duplicates , rows identical (ie, nulls).
if want output add together column trsm (tbl_received_sms) columns returned. trsm.cust_prod_id 1 try.
edit bit more details explain unions.
take illustration heavily simplified version of query:-
select tcp.id, tc.name tbl_cust_products tcp left bring together tbl_received_sms trsm on tcp.id = trsm.cust_prod_id left bring together tbl_customer tc on tcp.cust_id=tc.id union select tcp.id, concat(tc.firstname,' ',tc.lastname) cust_id tbl_cust_products tcp right bring together tbl_received_sms trsm on tcp.id = trsm.cust_prod_id left bring together tbl_customer tc on tcp.cust_id=tc.id
say tables contain following
tbl_cust_products id name cust_id 1 5 2 b 6 tbl_received_sms id cust_prod_id info 3 2 c 4 3 d 5 4 e tbl_customer id name 5 fred 6 burt
the first query homecoming both records tbl_cust_products, 1 of matched against tbl_received_sms:-
id name 1 fred 2 burt
the 2nd query find 3 records tbl_received_sms, 1 of matched against tbl_cust_products. records unmatched have null in both returned fields (as there no matching record on tbl_cust_products value of field there null, , same value of field tbl_customer match non existant record tbl_cust_products). record matches populated:-
id name null null null null 2 burt
the union merge these 2 lots together,
id name 1 fred 2 burt null null null null 2 burt
but eliminating duplicates, hence:-
id name 1 fred 2 burt null null
php mysql sql join full-outer-join