Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / SQL union in Query
Small and medium business | Business Central, N...
Suggested answer

SQL union in Query

(0) ShareShare
ReportReport
Posted on by Hein Kruger 511

Hi everyone,

I started working with Queries recently and got this request form a user. They want to do a union between two unrelated tables (Example Customer and Vendor).

From what I have read there is no Union function in BC Queries.

pastedimage1642602754792v1.png

To get the output on the image I created a new table called "Source" with Source Type, Source_No and Source_Name as the fields.
In the OnBeforeOpen trigger in the Query I did the following to insert lines into the new "Source" Table. In other words the "Source" Table get recreated every time the Query is opened.

pastedimage1642602956733v2.png

Now I get the results that I wanted but I'm wondering if there is a better more effective way to do this?
Thank you in advance

  • Gert Lynge - www.dabbler.dk Profile Picture
    Gert Lynge - www.da... 80 on at
    RE: SQL union in Query

    If you are OnPrem a SQL View doing the magic with a linked BC table is probably more efficient (but make sure you define a unique key that is really unique - been bit by that in the past).

    Yes, I know we are not "allowed" to suggestion cloud incompatible solutions anymore :-)

  • Samantha73 Profile Picture
    Samantha73 2,677 on at
    RE: SQL union in Query

    Tem table is better in my opinion as if two users run the report at the same time records can deleted before being displayed in the report.

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 79,097 Super User 2025 Season 1 on at
    RE: SQL union in Query

    Hi, Or create two Query first, vendor and customer, then create a new table (TableType = Temporary) and a new page to display the data in the table.

    On the trigger OnOpenPage() of the new page, using Query.Open() Method to insert data to the new table.

    However, I still think your method is simpler.

    Hope this will help.

    Thanks.

    ZHU

  • Suggested answer
    JAngle Profile Picture
    JAngle 63 on at
    RE: SQL union in Query

    I’d agree with the method you have opted for. Very limited pathways in this scenario.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,209 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans