luka Good day 🙂
Here's how you can structure a SELECT query with JOIN in a dataflow using Python:
import sql_query
import catalogs
(Create a list of fields we want to retrieve)
fields = ("Id", "Email", "Department")
(List of fields from the second component we are joining)
fields_join = (("Id", "DepartmentName"),)
(GUID of the main component)
component = 'cee394d2-64fd-49d0-9221-1068ae010495'
(Specify which component to join and which fields to compare)
join_on = {'123123-456456-456-9221-100005': ('Department', 'Id')}
(Get the SQL query as a string)
query_str = sql_query.simple_join_query(fields, component, fields_join, join_on)
(Create the query)
query = catalogs.query(query_str)
(Execute the query)
item["query_result"] = query.execute()
(Result)
"""
SELECT cmp."Id" as "Id",
cmp."Data_Email" as "Email",
cmp."Data_Department" as "Department",
jn1."Id" as "jn1_Id",
jn1."Data_DepartmentName" as "DepartmentName"
FROM "cee394d2-64fd-49d0-9221-1068ae010495" as cmp
INNER JOIN "123123-456456-456-9221-100005" as jn1 ON cmp."Data_Department" = jn1."Id"
"""