title: join two tables in one with one id and multiple ids in another table
body: <p>I have a table <code>content</code> that is joined with <code>content_image</code>. There can be different images for a single <code>content_id</code>. I want to join them with case, according to <code>image_size</code>.</p>
  
  <p>In other words, I have one content, with 3 different images sizes. But these images are in another table, so I want to show only one content, with 3 different images in one output.</p>
  
  <p>Anyway hope the code will be more clear in my explanation:</p>
  
  <pre><code>    SELECT C.CHAPTER_ID,CH.CHAPTER,C.CONTENT_ID,
             C.CONT_HEAD AS NAME,
             SMALL=(CASE WHEN CI.IMAGE_SIZE=0 THEN CONTIMAGE_SMALL END),
             BIG=(CASE WHEN CI.IMAGE_SIZE=2 THEN CONTIMAGE_SMALL END),
             C.UPDATE_DATE,
             C.RECORD_DATE
      FROM   CONTENT C
                 LEFT OUTER JOIN CONTENT_CHAPTER CH ON C.CHAPTER_ID = CH.CHAPTER_ID
                 LEFT OUTER JOIN CONTENT_IMAGE CI ON CI.CONTENT_ID=C.CONTENT_ID
      WHERE  CH.CONTENTCAT_ID = 14
      ORDER BY C.UPDATE_DATE DESC,C.RECORD_DATE DESC
  </code></pre>
  
  <p>and the output code: </p>
  
  <pre><code>    &lt;cfoutput query="get_images"&gt;
          &lt;a class="highslide" target="_blank" onclick="return hs.expand(this, { slideshowGroup: 1 } )" href="/documents/content/#big#"&gt;&lt;img src="/documents/content/#small#" title="#name#" border="0" /&gt;&lt;/a&gt;
      &lt;/cfoutput&gt;
  </code></pre>
  
  <p>The problem is this generates two outputs. In first one, only the <code>big</code> value is populated. In the second one, only the <code>small</code> value is populated. If I group the cfoutput by <code>content_id</code>, then it does generate a single result, but again - only the <code>big</code> value is defined :) But I want a <em>single</em> output with both <code>big</code> and <code>small</code> values defined. </p>
  
  <p>Thank you all for help!</p>