MySQL – Join Two SELECT Queries with Aliases

MySQL

I have two queries and both are working fine individually, but I want to join both the queries and display the output in a single grid.

Query1

SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
SELECT item_master_id,
   SUM(received_quantity) AS received_quantity,
   SUM(ordered_quantity)  AS ordered_quantity,
   SUM(unit_cost)  AS unit_cost
FROM material_line_item
GROUP BY item_master_id) t2
ON t1.id = t2.item_master_id

In this query1 I am using two tables to fetch records

Query2

SELECT item_master_id
,SUM(open_quantity) open_quantity
FROM closed_stock
GROUP BY item_master_id

And in query2 from a single table

In all the above 3 tables 3 columns are there received_quantity ordered_quantity and open_quantity so I have to show all this 3 quantity columns in a single query by joining this above two queries.

So far what I tried

SELECT * FROM (SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
SELECT item_master_id,
   SUM(received_quantity) AS received_quantity,
   SUM(ordered_quantity)  AS ordered_quantity,
   SUM(unit_cost)  AS unit_cost
FROM material_line_item
GROUP BY item_master_id) t2
ON t1.id = t2.item_master_id)

join 

(SELECT item_master_id
,SUM(available_quantity) open_quantity
FROM closed_stock
GROUP BY item_master_id)

Best Answer

It would be better to simplify the query without nested levels of subqueries. You already have as simple table (t1) joined to a derived table (t2). Join them further to the other query2 as a derived table (lets call it t3 in lack of a better name):

SELECT 
    t1.*, 

    t2.received_quantity,
    t2.ordered_quantity,
    t2.unit_cost,

    t3.open_quantity
FROM 
        item_master AS t1
    INNER JOIN
        (
        SELECT item_master_id,
           SUM(received_quantity) AS received_quantity,
           SUM(ordered_quantity)  AS ordered_quantity,
           SUM(unit_cost)         AS unit_cost
        FROM material_line_item
        GROUP BY item_master_id
        ) AS t2
    ON t1.id = t2.item_master_id

    INNER JOIN
        (
        SELECT item_master_id,
               SUM(open_quantity) AS open_quantity
        FROM closed_stock
        GROUP BY item_master_id
        ) AS t3
    ON t1.id = t3.item_master_id ;

If there are items in the item_master table that don't appear in one or both the other two tables, you might want to change these INNER joins to LEFT joins.

Related Question