php - Full Outer Join with multiple tables in mysql -

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