How to use outer query's columns in subqueries?

mysql, sql

Solution

This works on both `5.0` and `5.1`:

CREATE TABLE t1 (t1id INT NOT NULL, t1c1 VARCHAR(20) NOT NULL);

CREATE TABLE t2 (t2id INT NOT NULL, t2c1 INT NOT NULL, t1id INT NOT NULL);

SELECT  t1c1,
        (
        SELECT  GROUP_CONCAT(t2c1)
        FROM    t2
        WHERE   t1.t1id = t2.t1id
        )
FROM    t1

You may try rewriting it as a `JOIN`:

SELECT  t1c1, GROUP_CONCAT(t2c1)
FROM    t1
LEFT JOIN
        t2
ON      t2.t1id = t1.t1id
GROUP BY
        t1.t1id

Problem

I have a table with inventory records and another table with documents describing each inventory records, something like: ``` inventory (t1) t1id t1c1 1 desc1 2 desc2 3 desc3 documents (t2) t2id t2c1 t1id 1 doc1 1 2 doc2 1 3 doc3 2 4 doc4 3 ``` so i try this query an I get an error telling me that there is no such column (t1.t1id) near the sub query =s ``` SELECT t1c1,(SELECT GROUP_CONCAT(t2c1) FROM t2 WHERE t1.t1id = t2.t1id) FROM t1 ``` any Ideas?

Original source