Merge two select query result in postgres

Question

Need to merge two select query result in postgres with out using union or union all bcz datatype are different.i can do it in php still trying to get in query result itself.

I tried with union it says datatype are different

QUERY 1:

(select     a.id,a.op_id,a.consult_id,b.id,b.finding_value,b.test_name_id,b.methd_id,b.sngl_tst_id,b.grp_tst_id,b.cmplx_tst_rslt_id,b.test_type 
    from tra_rslt_entry_hd a
        join tra_rslt_entry_dt b on b.result_id = a.id 
    where b.test_type = 'S' and a.consult_id = 5849 )

QUERY 2:

(select distinct a.op_id,a.consult_id,d.test_name_id,d.specimen,max(g.male)as male,max(g.female) as female,max(g.common) as common ,max(g.adult) as adult,max(g.child) as child,max(g.gender_specific)as gender_specific, d.test_name 
    from tra_op_ip_consult_head a
        join  tra_op_ip_diagno_detail c on c.consult_id=a.consult_id
        join mas_test_name d on d.test_name_id = c.test_name_id 
        join mas_tst_rgnt_nmval g on  g.test_name_id =  d.test_name_id
    where a.consult_id =5849 and d.dept_id = 6 group by a.consult_id,a.op_id,d.test_name_id,d.test_name,d.specimen
    order by d.test_name_id)

Isidoro Arroyo Barrantes · Accepted Answer · 2017-04-08 07:18:39Z

3

To use UNION operator, all queries must have same number of columns and the columns must be of compatible type, so you must use type casting.

Lets suppose that the 4th column in query1 (b.id) is numeric and the 4th column in query2 (d.specimen) is text. Union will fail because they aren't compatible types. You must use typecasting:

select a.id,a.op_id,a.consult_id,b.id::text, ...
union 
select distinct a.op_id,a.consult_id,d.test_name_id,d.specimen,...

edited Apr 8, 2017 at 7:18

answered Apr 8, 2017 at 7:12

Isidoro Arroyo Barrantes

3551 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge two select query result in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related