CODEWITHSUNDEEP
coldfusionlucee
JimSofbrez
JimSofbrez

Reputation: 41

Merge Queries using query of queries

I am trying to merge two queries using lucee but there is a bug in lucee that does not do any distinct

it just returns the union all even for union

trying it like this

select * from tbl1
union 
select * from tbl2

ending up as:

Apple
Apple
Orange
Banana

is there anything in java i can use directly to make the union work, there is a bug but its in backlog and that will not sure will take how much time to fix

Upvotes: 1

Views: 481

Answers (1)

rrk
rrk

Reputation: 15875

You can try to use group by

GROUP BY columnName

<cfquery name="test" dbtype="query">
    SELECT * FROM a
    UNION
    SELECT * FROM b
    GROUP BY fruitName
</cfquery>

UPDATE from comments

<cfquery name="test" dbtype="query">
    SELECT * FROM a
    UNION DISTINCT
    SELECT * FROM b
    GROUP BY fruitName
</cfquery>

DEMO

DEMO Union Distinct

Upvotes: 2

Related Questions