CODEWITHSUNDEEP
postgresql
satish kilari
satish kilari

Reputation: 728

Merge two select query result in postgres

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)

Upvotes: 2

Views: 1930

Answers (1)

Isidoro Arroyo Barrantes
Isidoro Arroyo Barrantes

Reputation: 355

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,...

Upvotes: 3

Related Questions