1

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)
  • Could you please add create table scripts, sample data and expected output? – Tom V - Team Monica Aug 2 '16 at 11:59
  • @TomV the problem is solved I got the output from the below answer. But thanks anyway for your interest on it. – Ranjan Aug 2 '16 at 12:06
3

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.

  • Great :) @ypercube – Ranjan Aug 2 '16 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.