Hi
Just wondering if advanced queries support the 'with' clause
eg
WITH cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
Hi
Just wondering if advanced queries support the 'with' clause
eg
WITH cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
Hello lisa hepplewhite ,
Thanks for reaching out.
I just tried on a Sugar 14 and for the following example query:
WITH account_data AS (
SELECT id, name, description
FROM accounts
),
account_custom_data AS (
SELECT id_c, customfield_c
FROM accounts_cstm
)
SELECT ad.id, ad.name, ad.description, acd.customfield_c
FROM account_data AS ad
JOIN account_custom_data AS acd ON ad.id = acd.id_c;
The only thing you might need to be mindful of is the underlying MySQL version of you Sugar Instance as I believe that this is only supported on MySQL 8.0 and above.
Let me know if this helps.
Cheers,
André
