I have two tables i use in jqgrid.
我在jqgrid中使用了两个表。
Table 1: id->0,1,2; state->1,1,0
Table 2: id->0,1,2,3; product->apple,banana,cherry,melon;
I want to find the id's which have state 1 and retrieve the corresponding product from the other table. Is there a proper way to do it? $result below always returns zero. I am connected to the database successfully.
我想找到状态为1的id,并从另一个表中检索相应的产品。有没有正确的方法呢?下面的$ result总是返回零。我已成功连接到数据库。
Here is the code:
这是代码:
$var = "SELECT id FROM table1 WHERE state = 1";
$result = mysql_query($var);
$grid->setSelect('order', "SELECT id, product FROM table2 WHERE id='$result' ");
1 个解决方案
#1
2
You can run one query:
您可以运行一个查询:
SELECT t2.id, t2.product
FROM table2 t2
WHERE t2.id = (SELECT t1.id FROM table1 t1 WHERE t1.state = 1);
If the subquery could conceivably return more than one result, then use in instead of =:
如果子查询可以想象返回多个结果,则使用in而不是=:
SELECT t2.id, t2.product
FROM table2 t2
WHERE t2.id IN (SELECT t1.id FROM table1 t1 WHERE t1.state = 1);
#1
2
You can run one query:
您可以运行一个查询:
SELECT t2.id, t2.product
FROM table2 t2
WHERE t2.id = (SELECT t1.id FROM table1 t1 WHERE t1.state = 1);
If the subquery could conceivably return more than one result, then use in instead of =:
如果子查询可以想象返回多个结果,则使用in而不是=:
SELECT t2.id, t2.product
FROM table2 t2
WHERE t2.id IN (SELECT t1.id FROM table1 t1 WHERE t1.state = 1);