在没有连接和使用子查询的情况下编写查询

I want to rewrite the join query with sub-query like IN(), ANY() operator.

我想用IN(),ANY()运算符之类的子查询重写连接查询。

Data set:

*categories*
categoryID name 
         5 ROD    
         7 CEMENT 

*products*
productID categoryID name      
        7          5 BSRM 10mm 
        9          5 KSRM 5mm  
       10          5 Julius    
       11          7           
       12          5 BSRM 25mm 

*sale_products*   
saleID productID 
   118         9 
   119         9 
   120         9 
   121         9 
   122        12 
   123        12 
   124        12

This is my query to read saleID, product name and category name for which already have sold.

这是我查询已经售出的saleID,产品名称和类别名称的查询。

My query is:

我的查询是:

SELECT sale_products.saleID, products.name, categories.name
FROM categories
  INNER JOIN (products, sale_products)
    ON  categories.categoryID = products.categoryID
    AND products.productID = sale_products.productID

Now I want to the result set without join and with the sub-query methodology.

现在我想要没有连接的结果集和子查询方法。

I try in this way:

我试着这样:

SELECT categories.name
FROM categories
WHERE categories.categoryID IN
          (SELECT products.categoryID
           FROM products
           WHERE products.productID in
               (SELECT sale_products.productID FROM sale_products))

this query only give me the category name but I need also saleID, product name.

此查询只给我类别名称,但我还需要saleID,产品名称。

1 个解决方案

#1

Your original query looks queer. Why do you cross join products and sale_products?

您的原始查询看起来很奇怪。你为什么要加入产品和sale_products?

It should better be:

最好是:

select sp.saleid, p.name as product, c.name as category
from sale_products sp
join products p on p.productid = sp.productid
join categories c on c.categoryid = p.categoryid;

This is the straight-forward way to show the data. You can use subqueries instead, but I see no sense in it:

这是显示数据的直接方式。您可以使用子查询,但我认为没有任何意义:

select 
  sp.saleid
  (
    select p.name
    from products p
    where p.productid = sp.productid
  ) as product, 
  (
    select c.name
    from categories c 
    where c.categoryid = p.categoryid
  ) as category
from sale_products sp;

Here is yet another query with subqueries. Again without any benefit over the simple query using direct joins on the tables.

这是另一个带子查询的查询。再次对使用表上的直接连接的简单查询没有任何好处。

select sp.saleid, p.name as product, c.name as category
from sale_products sp
join (select productid, name from products) p on p.productid = sp.productid
join (select categoryid, name from categories) c on c.categoryid = p.categoryid;

#1