Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Join two select queries horizontally in Postgresql

I have following two queries:

Query #1:

(SELECT 
    pl.c_project_Id, pl.c_projectphase_Id, pl.c_projecttask_Id, pl.m_product_Id,
    pj.name as projectname, ph.name as phasename, pt.name as taskname, pd.name as prodname,
    round(pl.plannedqty, 2) as planqty, round(pl.plannedprice, 2) as planrate,
    round(pl.plannedamt, 2) as planamt

FROM adempiere.c_projectline pl
LEFT JOIN adempiere.c_project pj ON pl.c_project_id = pj.c_project_id
LEFT JOIN adempiere.c_projectphase ph ON pl.c_projectphase_id = ph.c_projectphase_id
LEFT JOIN adempiere.c_projecttask pt ON pl.c_projecttask_id = pt.c_projecttask_id
LEFT JOIN adempiere.m_product pd ON pl.m_product_id = pd.m_product_id
WHERE pl.c_project_id = 1000001 AND pl.ad_client_id = 1000000
ORDER BY ph.c_projectphase_id, pt.c_projecttask_id)

Output is: 11 columns and 16 rows

Query #2:

(SELECT
    fa.c_project_id, fa.c_projectphase_id, fa.c_projecttask_id, fa.m_product_id,
    pj.name as costprojectname, ph.name as costphasename, pt.name as costtaskname,
    pd.name as costprodname,
    abs(fa.qty) as costqty, round((fa.amtacctdr/fa.qty), 2) as costrate,
    round(sum(fa.amtacctdr), 0) as costamt

FROM adempiere.fact_acct fa
LEFT JOIN adempiere.c_project pj ON fa.c_project_id = pj.c_project_id
LEFT JOIN adempiere.c_projectphase ph ON fa.c_projectphase_id = ph.c_projectphase_id
LEFT JOIN adempiere.c_projecttask pt ON fa.c_projecttask_id = pt.c_projecttask_id
LEFT JOIN adempiere.m_product pd ON fa.m_product_id = pd.m_product_id
WHERE  fa.c_project_id = 1000001 AND (fa.gl_category_id = 1000006 OR fa.gl_category_id = 1000005)
AND fa.qty > 0 AND fa.c_project_id is not null
GROUP BY fa.m_product_id, fa.c_project_id, fa.c_projectphase_id, fa.c_projecttask_id,
        fa.qty, fa.amtacctdr,
        pj.name, ph.name, pt.name, pd.name)

Output is: 11 columns and 6 rows

I want to join these queries horizontally, display all columns but rows should not duplicate. As when I apply union to join them the result shows duplicate rows. How can I cope with this issue?

like image 404
see2rizwan Avatar asked Dec 16 '14 07:12

see2rizwan


People also ask

How do I join two queries in PostgreSQL?

The UNION operator combines result sets of two or more SELECT statements into a single result set. To combine the result sets of two queries using the UNION operator, the queries must conform to the following rules: The number and the order of the columns in the select list of both queries must be the same.

How can I merge two queries without UNION?

You need to create two separate queries and join their result not JOIN their tables. Show activity on this post. JOIN and UNION are differents. In your query you have used a CROSS JOIN operation, because when you use a comma between two table you apply a CROSS JOIN.


1 Answers

You should be able to join queries like this:

select * from
  (
   <your first query here>
  ) tbl1
  join (
    <your second query here>
  ) tbl2
  on tbl1.c_project_Id = tbl2.c_project_Id
 and tbl1.c_projectphase_Id = tbl2.c_projectphase_Id -- you might add or
 and tbl1.c_projecttask_Id  = tbl2.c_projecttask_Id  -- remove join criteria 
 and tbl1.m_product_Id = tbl2.m_product_Id           -- here
like image 73
alzaimar Avatar answered Oct 26 '22 23:10

alzaimar