SQL how to select a single value from a table with LEFT JOIN, In Google Datastudio

I have two tables one table called mailing_events and one table called mailing_outletcontact. Examples of my tables are below.

The mailing table is very simple with no duplicates:

+-------+------------+--------------------------+
|  id   | mailing_id |          email           |
+-------+------------+--------------------------+
| name1 |         12 | [e-mail]  |
| name2 |         15 | [e-mail]          |
| name3 |         20 | [e-mail]          |
+-------+------------+--------------------------+

My Second table “mailing_outletcontact” has duplicates in the email field.

+----+-------------------------+------------------+--------------+
| id |          email          | outletcontact_id | email_number |
+----+-------------------------+------------------+--------------+
|  1 | [e-mail] |                6 |            5 |
|  2 | [e-mail] |                6 |            6 |
|  3 | [e-mail] |                6 |            7 |
|  4 | [e-mail]         |                8 |            8 |
|  5 | [e-mail]         |                4 |            9 |
|  6 | [e-mail]         |                8 |           10 |
+----+-------------------------+------------------+--------------+

I am trying to query the database in Datastudio with my goal being to get the “outletcontact_id” field with my first table data.

I tried to do a left join, however, since there are multiple values in the second table I had to pick a row to match. To me it doesn’t matter which row it matches, I decided to pick the one with the highest id field.

My code is:

SELECT
    mailing_events.mailing_id,
    mailing_events.email,
    new_mailing_outletcontact.outletcontact_id
FROM
    mailing_events
LEFT JOIN(
    select  *
    from  mailing_outletcontact
    where id in(select max(id) from mailing_outletcontact group by email)   
) as new_mailing_outletcontact 
    on mailing_events.email = new_mailing_outletcontact.email;
    
SELECT
    mailing_events.mailing_id,
    mailing_events.email,
    new_mailing_outletcontact.outletcontact_id
FROM
    mailing_events
LEFT JOIN(
    select *
    from mailing_outletcontact
    where id in(select max(id) from mailing_outletcontact group by email)   
) as new_mailing_outletcontact 
    on mailing_events.email = new_mailing_outletcontact.email;

This didn’t work, does anyone know where I went wrong. Or how to fix my issue altogether. which is to get the “outletcontact_id” field with my first table data.

EDIT: I am running the SQL in Datastudio so the error message isn’t great. The error ID also doesn’t provide any value after looking online. The error message is:

The query returned an error.

Error ID: 3ab6a2cd

Second EDIT: The Answer provided by shawnt00 does work in SQL client software such as DBeaver. So if you are reading this with a similar issue that should help.

It still does not work in Datastudio with their SQL connection, so they might use a different standard or something?

Answer

SELECT
    me.mailing_id, me.email,
    (
        select max(moc.outletcontact_id)
        from mailing_outletcontact moc
        where moc.email = me.email
    ) as outletcontact_id
FROM
    mailing_events me;