title: MySQL Join with denormalized join attribute
body: <p>I need to perform a database join where unfortunately the database structure is not in the first normal form. I cannot change the table structure/layout because this is application generated and I can only create read-only queries to fetch data for my own statistic component.</p>
  
  <p>The schema looks like the following. There is the table <code>services</code> which consists several attributes where especially the attribute <code>property</code> is relevant. The following are example rows of the <code>report</code> table:</p>
  
  <pre><code>table: report
  
  id | vehicles | ...
  -------------------
  1  | 1,2,3    | ...
  2  | 2,3      | ...
  3  | 2        | ...
  </code></pre>
  
  <p>The <code>vehicle</code> tables looks like this:</p>
  
  <pre><code>table: vehicle
  
  vid | description
  ---------------
  1   | Vehicle A
  2   | Vehicle B
  3   | Vehicle C
  </code></pre>
  
  <p>My goal ist to count how often a vehicle was part of a report. The desired result rows are:</p>
  
  <pre><code>desired result
  
  description | count(*)
  ----------------------
  Vehicle A   | 1
  Vehicle B   | 3
  Vehicle C   | 2
  </code></pre>
  
  <p>The pseudo query for this would be something like:</p>
  
  <pre><code>SELECT v.description, Count(*)
  FROM report r
  JOIN vehicles v
  ON v.vid IN(r.vehicles)
  GROUP BY v.description;
  </code></pre>
  
  <p>Is there a MySQL way to handle this on the database side? Or do I have to select everything, explode the vehicles in the application login and perform the "join" outside the database?</p>
  
  <p>I would prefer it to do this on the database side. I can only read both tables but can create views, if necessary (which I want to avoid, too, if possivle). </p>