title: Mysql subquery per row
body: <p>I'm trying to show a matrix table with the results from a vendor in a category. Like:</p>
  
  <pre><code>+-----------+-------------+-------------+------+
  |           | Category 1  | Category 2  | ...  |
  +-----------+-------------+-------------+------+
  | Vendor 1  |       8900  |          0  | ...  |
  | Vendor 2  |      56890  |       4000  | ...  |
  |      ...  |        ...  |        ...  | ...  |
  +-----------+-------------+-------------+------+
  </code></pre>
  
  <p>I already made a query for just one vendor:</p>
  
  <pre><code>SELECT ut.unit_name, IFNULL(SUM(commission_fix_out),0)
  FROM transaction t
  JOIN office_per_transaction opt
  ON t.id = opt.transaction_id
  JOIN_vendor_per_transaction ppt
  ON t.id = ppt.transaction_id
  AND ppt.vendor_id = 2
  RIGHT JOIN unit_type ut
  ON t.unit_type_id = ut.id
  AND transaction_end_week BETWEEN 0 AND 14
  AND YEAR(transaction_end_date) = 2012
  GROUP BY ut.id
  </code></pre>
  
  <p>Which gives me following result:</p>
  
  <pre><code>+-------------+---------+
  | Category    | Result  |
  +-------------+---------+
  | Category 1  |  56890  |
  | Category 2  |   4000  |
  |        ...  |    ...  |
  +-------------+---------+
  </code></pre>
  
  <p>Now i would like to see a concatenation of this query per vendor in a given office.</p>
  
  <p>First, I tried replacing <code>AND ppt.vendor_id = 2</code> with <code>AND ppt.vendor_id IN (SELECT id FROM vendor WHERE office_id = 1)</code> but that gave me the total result of all the vendors in that office.</p>
  
  <p>Secondly, I tried using a subquery. But that gave me an error stating <strong>unknown column</strong>.</p>
  
  <p>What i want to achieve is a resultset like:</p>
  
  <pre><code>+-----------+--------- -------+
  | Vendor    | Results         |
  +-----------+-----------------+
  | Vendor 1  | 8900,0,...      |
  | Vendor 2  | 56890,4000,...  |
  | ...       | ...,...,...     |
  +-----------+-----------------+
  </code></pre>
  
  <p>Am i trying something unrealistic and should i rather perform above query per vendor. Or am i missing something here?</p>
  
  <p>@SashiKant I've tried many queries last days, but it was something like this: </p>
  
  <pre><code>SELECT v.id, GROUP_CONCAT(r.result) FROM vendor v, 
     ( SELECT ut.unit_name, IFNULL(SUM(commission_fix_out),0) as result 
       FROM transaction t JOIN office_per_transaction opt ON t.id = opt.transaction_id 
       JOIN vendor_per_transaction ppt ON t.id = ppt.transaction_id 
       AND ppt.vendor_id = p.id 
       RIGHT JOIN unit_type ut ON unit_type_id = ut.id 
       AND transaction_end_week BETWEEN 0 AND 14 
       AND YEAR(transaction_end_date) = 2012 GROUP BY ut.id) as r
  </code></pre>