Combining two mysql results

Question

I have two multi-join queries. 1 for products that come from the 'session' table, another for products that come from the 'package' table. Both queries have to join with my order and order_items tables.

A single order should look like this in our list of orders web page:

|order id  |  products|
|123       |  A2,PKG1 |

Each individual query group concats the products, but how can I group concat products from two different join queries?

My initial thought was a join table, but my co-worker said just use objects.

I really do not want to write code/construct objects since the products are just for display.

Can anyone make a suggestion that's quick an easy? Anything other than a join table or using objects.

Here are my 2 queries:

sessions: select ats_store.order.payment_type,production_www.paypal_transactions.createdOn as order_date,production_www.paypal_transactions.FIRSTNAME as firstname, production_www.paypal_transactions.LASTNAME as lastname,ats_store.order.id as order_id,ats_store.order.customerid,ats_store.order.shipping_address_id,ats_store.order.status,ats_store.order_items.product_id,ats_store.shipping_address.country as country,ats_store.shipping_address.street_1 as street1,ats_store.shipping_address.street_2 as street2,ats_store.shipping_address.street_3 as street3,ats_store.shipping_address.city as city,ats_store.shipping_address.state_and_province as state,ats_store.shipping_address.zip_code as zip,GROUP_CONCAT(production_cache_salesforce_repl.ats_conference_session__c.Name order by production_cache_salesforce_repl.ats_conference_session__c.Sort_Code__c) as products,production_cache_salesforce_repl.ats_conference_session__c.Sort_Code__c from ats_store.order LEFT JOIN ats_store.shipping_address ON ats_store.shipping_address.id = ats_store.order.shipping_address_id INNER JOIN ats_store.order_items ON ats_store.order_items.order_id = ats_store.order.id INNER JOIN production_cache_salesforce_repl.ats_store_price__c price ON price.Id = ats_store.order_items.product_id JOIN production_cache_salesforce_repl.ats_conference_session__c ON production_cache_salesforce_repl.ats_conference_session__c.Id = price.ATS_Conference_Session__c LEFT JOIN production_www.paypal_transactions ON production_www.paypal_transactions.id = ats_store.order.paypal_transaction_id where ats_store.order.shipping_address_id != -1 and production_www.paypal_transactions.COMMENT1 = 'ATS Web Store' and production_www.paypal_transactions.RESPMSG = 'Approved' group by ats_store.order.id order by order_date

packages: select ats_store.order.payment_type,production_www.paypal_transactions.createdOn as order_date,production_www.paypal_transactions.FIRSTNAME as firstname, production_www.paypal_transactions.LASTNAME as lastname,ats_store.order.id as order_id,ats_store.order.customerid,ats_store.order.shipping_address_id,ats_store.order.status,ats_store.order_items.product_id,ats_store.shipping_address.country as country,ats_store.shipping_address.street_1 as street1, ats_store.shipping_address.street_2 as street2,ats_store.shipping_address.street_3 as street3,ats_store.shipping_address.city as city, ats_store.shipping_address.state_and_province as state,ats_store.shipping_address.zip_code as zip, GROUP_CONCAT(production_cache_salesforce_repl.ats_store_package__c.Package_Code__c order by production_cache_salesforce_repl.ats_store_package__c.Package_Code__c) as products from ats_store.order LEFT JOIN ats_store.shipping_address ON ats_store.shipping_address.id = ats_store.order.shipping_address_id INNER JOIN ats_store.order_items ON ats_store.order_items.order_id = ats_store.order.id INNER JOIN production_cache_salesforce_repl.ats_store_price__c price ON price.Id = ats_store.order_items.product_id JOIN production_cache_salesforce_repl.ats_store_package__c ON production_cache_salesforce_repl.ats_store_package__c.Id = price.ATS_Store_Package__c LEFT JOIN production_www.paypal_transactions ON production_www.paypal_transactions.id = ats_store.order.paypal_transaction_id where ats_store.order.shipping_address_id != -1 and production_www.paypal_transactions.COMMENT1 = 'ATS Web Store' and production_www.paypal_transactions.RESPMSG = 'Approved' group by ats_store.order.id order by order_date

Answer 1

To simplify, do the folowing:

1) Create a view for each query that you want to join

2) Once you create the views:

SELECT ProductID, SUM(Amout) FROM 
(SELECT * FROM vw_View1
UNION ALL
SELECT * FROM vw_View2
UNION ALL
SELECT * FROM vw_View3) GROUP BY ProductID

Just make sure all the views have the same number of fields and names.

Does this fits your needs?

Answer 2

SELECT order_id, products FROM session

UNION

SELECT order_id, products FROM package