How to use outer query's columns in subqueries?
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_CONC开发者_如何学CAT(t2c1) FROM t2 WHERE t1.t1id = t2.t1id) FROM t1
any Ideas?
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
You have to specify the table names (documents, inventory)
SELECT t1c1, (SELECT GROUP_CONCAT(t2c1)
FROM documents t2
WHERE t1.t1id = t2.t1id)
FROM inventory t1
精彩评论