title: JOIN 2 tables based on req.query in a 3rd table
body: <p>I have 3 tables (deals, deals_media, access_to_deals)</p>
  <pre><code>deals                     deals_media                     access (to deals)
  name     pic_url          type   media_url  deal_id       customer_id       deal_id
  deal1    http://blah      video  url.       1             1                 1
  deal2    http://blah2     img    url2.      1             1                 2
  deal3    http://blah3     video  url3.      2             2                 1
  </code></pre>
  <p>I'm sending an api get request with a <code>customer_id</code> query parameter.</p>
  <p>I want to send back an array of objects with all the <code>deals</code> data the customer is eligible for and to <code>join</code> an array of matching <code>deals_media</code> to the <code>deals.id</code></p>
  <p>I'm using sequalize and await functions and my query looks like:</p>
  <pre class="lang-js prettyprint-override"><code>sync (req, res) =&gt; {
      try {
          const customerId = req.query.customer_id;
          let deals = await db.sequelize.query(
              'SELECT DISTINCT deals.* FROM deals \n' +
              'JOIN access ON deals.id = access.deals_id AND access.customerId=$1 \n' +
              'LEFT JOIN media ON deals.id = media.deals_id \n',
              { bind: [customerId], type: 'RAW' },
          );
          res.send(campaign[0]);
      } catch (err) {...}
  };
  </code></pre>
  <p>my response data looks like:</p>
  <pre><code>   {
         &quot;id&quot;:1,
         &quot;deal_name&quot;: &quot;deal1&quot;,
         &quot;other data&quot;: &quot;data&quot;,
         &quot;media type&quot;: &quot;video&quot;,
         &quot;media URL&quot;: &quot;https://www.......&quot;,
         &quot;deals_id&quot;: 1
      },
      {
         &quot;id&quot;:2,
         &quot;deal_name&quot;: &quot;deal1&quot;,
         &quot;other data&quot;: &quot;data&quot;,
         &quot;media type&quot;: &quot;picture&quot;,
         &quot;media URL&quot;: &quot;https://www.......&quot;,
         &quot;deals_id&quot;: 1
      },
      {
         &quot;id&quot;:3,
         &quot;deal_name&quot;: &quot;deal1&quot;,
         &quot;other data&quot;: &quot;data&quot;,
         &quot;media type&quot;: &quot;audio&quot;,
         &quot;media URL&quot;: &quot;https://www.......&quot;,
         &quot;deals_id&quot;: 1
      }
  ]
  </code></pre>
  <p>and I'd prefer it came out as:</p>
  <pre><code>    {
         &quot;id&quot;:1,
         &quot;deal_name&quot;: &quot;deal1&quot;,
         &quot;other data&quot;: &quot;data&quot;,
         &quot;media&quot;: [
             {
                  &quot;media type&quot;: &quot;video&quot;,
                  &quot;media URL&quot;: &quot;https://www.......&quot;
             },
             {
                  &quot;media type&quot;: &quot;picture&quot;,
                  &quot;media URL&quot;: &quot;https://www.......&quot;
             },
             {
                  &quot;media type&quot;: &quot;audio&quot;,
                  &quot;media URL&quot;: &quot;https://www.......&quot;
             }
  ]
  </code></pre>
  <p>I've tried to change the last join to <code>'LEFT JOIN media ON deals.id = media.deals_id GROUP BY media.deals_id \n'</code> however I get an error to include <code>deals.id</code> and <code>media.id</code> to the GROUP BY and then it produces the same incorrect query result as above. I'm relatively new to SQL so any direction is greatly appreciated. Many thanks for considering my request.</p>
  <p>UPDATE: As to @NBK's suggestion, I've updated the query below however I'm running into the following error <code>Error is Deals: SequelizeDatabaseError: schema &quot;deals&quot; does not exist</code></p>
  <pre><code>'SELECT json_build_object ( \n' +
      &quot;'id', deals.id \n&quot; +
      &quot;'deals_name', deals.deals_name \n&quot; +
      &quot;'deals_icon_url', deals.deals_icon_url \n&quot; +
      &quot;'conversion_event', deals.conversion_event \n&quot; +
      &quot;'created_at', daels.created_at \n&quot; +
      &quot;'updated_at', deals.updated_at \n&quot; +
      &quot;'deleted_at', deals.deleted_at \n&quot; +
      &quot;'media', json_build_array ( \n&quot; +
          &quot;json_build_objecty ( \n&quot; +
              &quot;'media_type', media.media_type \n&quot; +
              &quot;'media_url', media.media_url \n&quot; +
          ') \n' +
      ') \n' +
  ') \n' +
  'FROM deals \n' +
  'INNER JOIN access ON access.deals_id = deals.id AND access.user_id=$1\n' +
  'INNER JOIN media ON media.deals_id = deals.id \n',
  </code></pre>