LEFT JOIN on multiple conditions with exceptions

[David Belyaev]

Trying to write a script to perform a LEFT JOIN on tables with multiple conditions and some exceptions. 

If someone has a template would greatly appreciate!!!

[Ed Sambuco]

Are you asking about jdbc connection or an actual SQL example?

A bit of a wild example, in mySQL, but here goes:

SELECT `appointments`.*,
            `community_member`.`name`, `community_member`.`phone_number`,
            `organization_community_members`.`organization_community_member_name`,
            `organizations`.`organization_name`,
            `appointment_state`.`appointment_state`
            FROM `appointments`
            LEFT JOIN `appointment_state`
                ON (`appointments`.`appointment_id` = `appointment_state`.`appointment_id`
                    AND (`appointment_state`.`user_id` = 0
                        OR (`appointment_state`.`user_type` = 'volunteer'
                            AND `appointment_state`.`user_id` = $volunteer_id)))
            LEFT JOIN `community_member`
                ON (`appointments`.`is_cm_business_profile` = 0 AND `appointments`.`community_member_id` = `community_member`.`member_id`)
            LEFT JOIN `organizations`
                ON (`appointments`.`is_cm_business_profile` = 1 AND `appointments`.`cm_business_profile_id` = `organizations`.`organization_id`) -- CMPO
            LEFT JOIN `organization_community_members`
                ON (`appointments`.`is_cm_business_profile` = 1 AND `appointments`.`community_member_id` = `organization_community_members`.`organization_community_member_id`) -- CMPO Community Member
            WHERE `appointments`.`end_date` >= DATE_FORMAT(NOW(), '%Y-%m-%d') -- deed end date upcoming/not overdue
                AND ((`appointments`.`appointment_status_id` = 1 AND `appointments`.`v_business_profile_id` = 0 AND `appointments`.`volunteer_id` = 0) -- new (open to all)
                    OR (`appointments`.`appointment_status_id` = 1 AND `appointments`.`v_business_profile_id` = 0 AND `appointments`.`volunteer_id` = $volunteer_id) -- new (requested)
                    OR (`appointments`.`appointment_status_id` = 2 AND `appointments`.`volunteer_id` = $volunteer_id)) -- accepted (by this volunteer)
                

On Wed, Sep 7, 2022 at 1:56 PM David Belyaev <david....@formlabs.com> wrote:

Trying to write a script to perform a LEFT JOIN on tables with multiple conditions and some exceptions. 

If someone has a template would greatly appreciate!!!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4eb96a29-4d8f-4b1c-9de6-03575536124bn%40googlegroups.com.

[David Belyaev]

Oh no, but thank you! 

I am trying to perform left join in the Google Sheets using the Apps Script so that there is a new sheet with merged data. 

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/PfXh2KfooEU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAANZsGN%2BCXxvwwfkfUgpEXyDae3bcKaDJo6aRVp-wMVfMCx%3DZw%40mail.gmail.com.

--

Kind regards,

David Belyaev

Email: david....@formlabs.com

Paid Acquisition Intern - Global Marketing - Growth Acquisition