MYSQL-在一个查询中合并2个表,但有2个WHERE / IN子句

塞尔吉奥

我正在寻找对2个表的一个MYSQL查询,并将结果作为一个对象/数组。我的问题是查询需要匹配两个表中命名不同的一组ID。假设我有一个urls表和一个products表,它们都有一个与我要搜索的ID相对应的列,但名称不同...在urls表中称为resource_id,在products表中称为productid。

URLS TABLE: id | resource_id | url

PRODUCTS TABLE: productid | title | description | thumbnail

因此,在php中,我有一个ID列表,该ID仅在urls.resource_idproducts.productid等于这些ID时才用于检索两个组合的表

这是我到目前为止所拥有的:

$ids = ["18552", "18554", "18555", "18556"];
$newIds = implode(",",$ids);

$q3 = " SELECT products.title as title, urls.url as url
       FROM urls, products
       WHERE urls.resource_id IN ($newIds)
       AND products.productid IN ($newIds)";

目前,这会根据$ids变量4次返回4个结果-总共有16个结果。我认为这与WHERE urls.resource_id IN ($newIds) AND products.productid IN ($newIds查询一部分有关,因为4 * 4 = 16,但是我可能错了。

像这样:

{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },

{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },

{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },

{ "title": "Prod 1", "url": "prod-one.html" },
{ "title": "Prod 2", "url": "prod-two.html" },
{ "title": "Prod 3", "url": "prod-three.html" },
{ "title": "Prod 4", "url": "prod-four.html" },

不十分确定如何做到这一点而无需重复...有什么建议吗?

另外-该数据库来自购物车系统,因此我尝试尽可能简化示例,而不会在我的问题/示例中引起任何疑问。

先感谢您!

nbk

请尝试此查询。它显示了两个表和$ tdis中都存在的所有条目

$ids = ["18552", "18554", "18555", "18556"];
$newIds = implode(",",$ids);

$q3 = " SELECT p.title as title, u.url as url
       FROM urls u INNER JOIN products p ON u. resource_id  = p.productid 
       WHERE u.resource_id IN ($newIds);";

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章