Thank you so much! I will give this a try.
You have been extremely helpful.
Original Message:
Sent: 03-25-2025 12:53
From: Sigrid Kok
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Hi Michelle
You can do it in two pieces - as I've done this a number of times for different use cases.
The first query does everything but the remark and includes a vlookup for the remark value, creating a key with customer, address # and sequence # of the email address.
The second query leverages the customer numbers and address # as inputs and pulls in the remarks in a table in Excel, generating a key with the same fields - customer, address #, sequence # of the email
After the 2nd query, the vlookup kicks in on the main page and you get the remark or a blank.
A simplified example is attached. For anyone looking at this, I created it in ECC, but the same concept can work in S4.
main sheet - see the vlookup for remark using customer #, address # and sequence # of email
second query output into an excel table that generates the same key so the vlookup can find the right row
HTH,
Sigrid
------------------------------
Sigrid Kok
*Precisely Software Inc.
Original Message:
Sent: 03-25-2025 09:39
From: Michelle Briggs
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Thank you so much for your help.
The requestor wanted the data from ADRCT in the query, but if it is keeping me from pulling all accounts regardless of whether they have an e-mail address stored, I will delete it and they will need to manually add the ADRCT information.
------------------------------
Michelle Briggs
KIK Holdco Company Inc
Original Message:
Sent: 03-24-2025 14:14
From: Sigrid Kok
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Thanks, Michelle.
I believe the left outer join should be between ADRC and ADR6 - with ADR6 having the email address. So customers have an address, thus an inner join, but as you are stating, they may not have an email address.
That works until you try to join ADR6 to ADRCT. Now those two tables have an inner join, which means there has to be a match between email and the remarks, which means email has to be filled in.
If you remove ADRCT and put the left outer join between ADRC and ADR6, you should get customers with and without emails.
I can't try your query because (1) my system is setup differently - config & master data, and (2) there is something in one of your tables that doesn't match standard ECC data model, but this is my best guess. Here is a partial replica:
You can download remarks via a transaction script or separate query script and then use an Excel vlookup to pull it in.
Best Regards,
Sigrid
------------------------------
Sigrid Kok
*Precisely Software Inc.
Original Message:
Sent: 03-24-2025 13:28
From: Michelle Briggs
Subject: Struggling with Table Joins to Return All Records in Studio Queries
I attached the SQL table to the response. Here is the Excel version.
------------------------------
Michelle Briggs
KIK Holdco Company Inc
Original Message:
Sent: 03-24-2025 13:26
From: Michelle Briggs
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Hi, Sigrid-
We are on ECC.
------------------------------
Michelle Briggs
KIK Holdco Company Inc
Original Message:
Sent: 03-24-2025 12:27
From: Sigrid Kok
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Hi Michelle
Can you share your query & also if you are on ECC or S4?
Thanks,
Sigrid
------------------------------
Sigrid Kok
*Precisely Software Inc.
Original Message:
Sent: 03-24-2025 12:05
From: Michelle Briggs
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Thank you. I have a left outer join in the query, but it's still not returning fields with no data.
The tables and joins are shown below. It will not let me create a left outer join with any table from ADR6 except for ADRCT.
------------------------------
Michelle Briggs
KIK Holdco Company Inc
Original Message:
Sent: 03-24-2025 11:08
From: Sigrid Kok
Subject: Struggling with Table Joins to Return All Records in Studio Queries
Hi Michelle
This is a safe space to ask questions. :)
In general, it sounds like a left outer join would work, which means it would join with the table holding the email address if there was an entry or not an entry.
You can right click on the join link and switch it to a left outer join. Please see the documentation here: https://help.precisely.com/r/t/1016755853/2023-12-31/Automate-Studio/Connect/Latest/en-US/Automate-Studio-with-Studio-Administrator-User-Guide/Create-joins-between-tables
Hope that helps,
Sigrid
------------------------------
Sigrid Kok
*Precisely Software Inc.
Original Message:
Sent: 03-24-2025 10:32
From: Michelle Briggs
Subject: Struggling with Table Joins to Return All Records in Studio Queries
I am struggling with figuring out the table joins to return all data points in a query, even if the fields contain no data. The query is a Customer Master query where I want the e-mail addresses included, but I also want accounts who don't have an e-mail address entered included in the results.
I apologize if this is a question that has been asked previously, but I didn't see it anywhere.
Any assistance that can be provided would be appreciated.
------------------------------
Michelle Briggs
KIK Holdco Company Inc
------------------------------