Details about this thread
  • State Not Answered
  • Replies 3 replies
  • Subscribers 314 subscribers
  • Views 309 views
  • Users 0 members are here
Related content from around SugarClub

Advance queries - With clause

lisa hepplewhite

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;

 

Parents
  • 0 Sugar Employee Badge

    Hello  , 

    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 results are being retrieved correctly. 




    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é 

Reply
  • 0 Sugar Employee Badge

    Hello  , 

    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 results are being retrieved correctly. 




    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é 

Children