title: SQL join without multiple rows
body: <p>I've three tables like DATA, SATURATION, VALUE, where:</p>
  
  <pre><code>CREATE TABLE DATA(
    data_id INT AUTO_INCREMENT,
    PRIMARY KEY(data_id)
  );
  
  CREATE TABLE VALUE(
    value_id INT AUTO_INCREMENT,
    data_id INT,
    sample INT,
    channel_1 DOUBLE,
    channel_2 DOUBLE,
    channel_3 DOUBLE,
    PRIMARY KEY (value_id),
    FOREIGN KEY(data_id) REFERENCES DATA(data_id)
  );
  
  CREATE TABLE SATURATION(
    saturation_id INT AUTO_INCREMENT,
    data_id INT,
    sample INT,
    channel_1 INT CHECK (channel_1 &gt;= 0 AND channel_1 &lt;= 1),
    channel_2 INT CHECK (channel_2 &gt;= 0 AND channel_2 &lt;= 1),
    channel_3 INT CHECK (channel_3 &gt;= 0 AND channel_2 &lt;= 3),
    PRIMARY KEY (saturation_id),
    FOREIGN KEY(data_id) REFERENCES DATA(data_id)
  );
  </code></pre>
  
  <p>I've query like:</p>
  
  <pre><code>SELECT s.sample AS 'S Sample', s.channel_1 AS '#Ch_1 S', s.channel_2 AS '#Ch_2 ES', s.channel_3 AS '#Ch_3 ES',
    v.sample AS 'V Sample', v.channel_1 AS '#Ch_1 V', v.channel_2 AS '#Ch_2 V', v.channel_3 AS '#Ch_3 V'
  FROM VALUE v
    LEFT OUTER JOIN SATURATION s
    ON v.data_id = s.data_id
    WHERE s.sample = v.sample AND s.data_id = 1;
  </code></pre>
  
  <p>Result is okay, if the number of samples are equal in one series. But I want something like this:   </p>
  
  <pre>
  -----------
  | ES Sample | #Ch_1 ES | #Ch_2 ES | #Ch_3 ES | EV Sample | #Ch_1 EV | #Ch_2 EV | #Ch_3 EV |
  -----------
  | 1 | 1 | 0 | 1 | 1 | 114.5 | 10.2 | 114.5 |
  -----------
  | 2 | 0 | 0 | 1 | 2 | 114.5 | 10.2 | 114.5 |
  -----------
  | 3 | 0 | 0 | 1 | 3 | 114.5 | 10.2 | 114.5 |
  -----------
  | 4 | 1 | 0 | 1 | 4 | 114.5 | 10.2 | 114.5 |
  -----------
  | null | null | null | null | 5 | 114.5 | 10.2 | 114.5 |
  -----------
  </pre>
  
  <p>How to do it? Normally without clause 's.sample = v.sample' i get <a href="https://i.stack.imgur.com/iHMd2.png" rel="nofollow noreferrer">this</a>. I don't want store saturation and value in one table but this very simple and foolish solution I guess.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Here's a query which produce <a href="https://i.stack.imgur.com/Bh7R6.png" rel="nofollow noreferrer">result</a> as I wanted (if I have more values in one serie than saturations):</p>
  
  <pre><code>SELECT v.data_id AS 'V ID', s.data_id AS 'S ID', s.sample AS 'S Sample', s.channel_1 AS '#Ch_1 S', s.channel_2 AS '#Ch_2 S', s.channel_3 AS '#Ch_3 S',
    v.sample AS 'V Sample', v.channel_1 AS '#Ch_1 V', v.channel_2 AS '#Ch_2 V', v.channel_3 AS '#Ch_3 V'
  FROM SATURATION s RIGHT JOIN VALUE v ON v.sample = s.sample
  WHERE (v.data_id = 1 OR s.data_id IS NULL) AND (s.data_id = 1 OR s.data_id IS NULL);
  </code></pre>