1

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

Improve this question
3
  • Which version of lucee are you on? Commented Jul 15, 2020 at 18:10
  • 1
    Why can't you do this in your database query? Commented Jul 15, 2020 at 19:35
  • i answered my own question Commented Jul 15, 2020 at 19:38

1 Answer 1

Reset to default
2

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

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

5 Comments

I am latest , I tried the groupby clause but i get another error
in your demo too, i see the apple appear two times
use UNION DISTINCT will fix it
Refer to trycf.com/gist/3c0deada9b8bd3f93e67b75af27bf5da/…. Both the group by and union distinct methods yield the desired result.
@DanBracuk Yes, I'll update UNION DISTINCT to my answer. I can remove it if you want to add as a new answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.