title: Need help with a sql query that has an inner and outer join
body: <p>I really need help getting this query right. I can't share actual table and column names, but will try my best to layout the problem simply.</p>
  
  <p>Assume the following tables. The tables and keys CANNOT be changed. Period. I don't care if you think it's a bad design, this question isn't a design question, it's on SQL syntax.</p>
  
  <ul>
  <li>Table A - Primary key named id1</li>
  <li>Table B - Contains two foreign keys, TableA.id1 and Foo.id2(ignore Foo, it doesn't matter for this) </li>
  <li>Table C - Contains two foreign keys, TableA.id1 and Foo.id2, additional interesting
  columns.</li>
  </ul>
  
  <p>Constraints: </p>
  
  <ol>
  <li>The SQL gets a set of id1s passed in as an argument. </li>
  <li>It must return a list of Table C rows.</li>
  <li>It must only return Table C rows where a Table B row exists with a matching TableA.id1 and Foo.id2 - There ARE rows in Table C that don't match Table B</li>
  <li>A row MUST be returned for every id1 passed in, even if no Table C row exists.</li>
  </ol>
  
  <p>At first I tried a Left Outer Join from Table A to Table B then an Inner Join to Table C. That violates the 4th rule above, as the Inner Join drops out those rows.</p>
  
  <p>Next I tried two Left Outer joins. This is closer, but has the side effect of including rows that match the Table A join to Table B, but don't have a corresponding Table C entry, which isn't what I want.</p>
  
  <p>So, here's what I came up with.</p>
  
  <pre><code>SELECT
    a.id1,
    c.*
  FROM
    TableB b
  INNER JOIN 
    TableC c USING (id1,id2)
  RIGHT OUTER JOIN
    TableA a USING (id1)
  WHERE
    a.id1 in (x,y,z)
  </code></pre>
  
  <p>I'm a bit wary of a Right Outer Join, as the documentation I've read says it can be replaced with a Left Outer, but it doesn't appear so for this case. It also seems a bit rare, which is making other devs nervous, so I'm being cautious.</p>
  
  <p>So, three questions in one.</p>
  
  <ol>
  <li>Is this correct?</li>
  <li>Did I use the Right Outer Join correctly?</li>
  <li>Is there a cleaner way to achieve the same thing?</li>
  </ol>
  
  <p>EDIT: DB is MySQL</p>