title: mysql - joins with sub queries with sub query where conditions using main query fields
body: <p>i have a complaint table</p>
  
  <pre><code>|------------------------|
  |    cid    |    desc    |
  |------------------------|
  |     1     |  faulty    |
  |     2     |  broken    |
  |     3     |  spoiled   |
  |------------------------|
  </code></pre>
  
  <p>and an assignment table</p>
  
  <pre><code>|------------------------------------|
  |     aid   |    cid    |    empid   |
  |------------------------------------|
  |     1     |     1     |     1      |
  |     2     |     1     |     5      |
  |     3     |     2     |     2      |
  |     4     |     2     |            |
  |     5     |     3     |     2      |
  |     6     |     3     |     7      |
  |------------------------------------|
  </code></pre>
  
  <p>each complaint can be assigned to atmost two employees
  i need to display a list in the below format</p>
  
  <pre><code>|---------------------------------------------------|
  |    cid    |    desc    |   emp1id   |    emp2id   |
  |------------------------|--------------------------|
  |     1     |  faulty    |     1      |      5      |
  |     2     |  broken    |     2      |             |
  |     3     |  spoiled   |     2      |      7      |
  |------------------------|--------------------------|
  </code></pre>
  
  <p>i wrote the query like this</p>
  
  <pre><code>select c.cid, c.desc, a1.empid as emp1id, a2.empid as emp2id
  from complaint c 
  left join (
      select aid, cid, empid
      from assignment aa 
      where aa.cid = c.cid 
      limit 0,1 
  ) as a1 on a1.cid = c.cid
  left join (
      select aid, cid, empid
      from assignment ab
      where ab.cid = c.cid
      limit 1,1
  ) as a2 on a2.cid = c.cid
  </code></pre>
  
  <p>but it is not working, i am getting error for c.cid in sub queries. how to do?</p>