I am trying to update some SQL that was created awhile back by a developer who neglected to create some important joins. Could someone please help me edit the SQL for a case where I have joined 2 tables - Project & User? I am sure this is pretty basic as I am a noob and not really a developer.
(I simplified the problem to declutter my question):
The Project Table has 3 fields: Contact, TDTL, Toxicologist. Each field contains just the numeric user ID of the person
The User Table has a the user ID field that connects it to the person's name.
I want my query output to have columns that show the person's name in text rather than user ID.
If I were to use the Aggregate Builder, it would look like this where I can assign an alias to the User table depending on which "user" I want to bring back:
How do I do this in SQL?
Here is what I started so far, but I'm lost as to how to differentiate the User Name based on which type of userID I want to bring back:
SELECT
{PQOTMasterProject}.[ProjID],
{PQOTMasterProject}.[Project],
{User}.[Name] (where user ID = project Contact) <placeholder with pseudocode for now
{User}.[Name] (where user ID = project TDTL) <placeholder with pseudocode for now
{User}.[Name] (where user ID = project Toxicologist) <placeholder with pseudocode for now
{PQOTMasterProject}.[Comments],
{PQOTMasterProject}.[LastModified],
FROM
{PQOTMasterProject}
LEFT JOIN
{User} ON {User}.[Id]={PQOTMasterProject}.[TDTL]
{User} ON {User}.[Id]={PQOTMasterProject}.[ContactName]
{User} ON {User}.[Id]={PQOTMasterProject}.[Toxicologist]
I tried a couple things and this is the most meaningful error message I've gotten so far:
I am a little thrown because of the curly brackets, which I wouldn't use in my normal SQL editor.
Thank you.
Hi @flowcooker
As Bas de jong suggested, you can use alias to rename the table same you did in aggregate.
Another approach is, you can convert the aggregate to SQL following the below steps
Step 1: Double click on "Executed SQL" on right-bottom
Step 2: You will get the executed SQL from here. Moreover if you want to convert your aggregate to SQL, Click "Convert aggregate to SQL". It will convert your aggregate to SQL.
Please find below your posted query with alias name.
TDTL.[Name] TDTL,
ContactName.[Name] ContactName,
Toxicologist.[Name] Toxicologist,
{PQOTMasterProject}.[LastModified]
FROM {PQOTMasterProject}
LEFT JOIN {User} ContactName ON ContactName.[Id]={PQOTMasterProject}.[TDTL]
LEFT JOIN {User} TDTL ON TDTL.[Id]={PQOTMasterProject}.[ContactName]
LEFT JOIN {User} Toxicologist ON Toxicologist.[Id]={PQOTMasterProject}.[Toxicologist]
Thank you for your sample code and teaching me how to convert the aggregate. Unfortunately, my company disabled the Conversion to SQL from Aggregate:
I did try the code you provided...
So I then added the Select list items also to my GROUP BY clause:
Then got this error message:
So I added an "as" to the Select statements
and removed the aliases from the GROUP BY
I didn't get an error message this time, but when I tested the SQL and redefined the Output Structure "Projects_Grid", I did not show data from any of the joined columns in my output:
I suspect I need to do something to "bind" the joined columns to the output structure? Could you or someone else please advise next steps?
I am attaching the full query as it is today to this comment so anyone who wishes to can review the SQL that produced the above test output.
I tested your query & it's working fine. I am getting data from joined columns. I will suggest to cross-check your data in table.
Kindly check attached .OML & check the server action in Logic Tab. Also check table structure. You can modify this .OML & share again if not working.
Thanks
@Muhammad Mahmudul Hasan thank you for your help. You were correct. I just wasn't filtering for rows where data was present, so the sample output was bringing the first few rows up as blank in those columns.
I will mark this one as solved. I appreciate all of your help.
Glad that you found the root cause.
Hi, you can use aliases:
{User} u1 ON u1.[Id]={PQOTMasterProject}.[TDTL]
{User} u2 ON u2.[Id]={PQOTMasterProject}.[ContactName]
In your select statement:
SELECT u1.[name], u2.[name]
Etc...