连接两个表并使用where子句过滤它们 [英] Join two tables and filter them with where clause

问题描述

我在获取所需的SQL输出时遇到问题.

I have problem getting the SQL output I want.

我有两个这样的表:

tblOrder

ID    User    Status
1      1         0
2      1         0
3      2         1

tblOrderItem

ID   OrderID     Product    Quantity
1       1           A           2
2       1           B           1
3       2           A           3
4       2           B           1
5       2           C           1
6       3           A           2

我想获得一个输出表，在其中我可以看到状态为0的所有订单，还可以看到该订单中的所有项目，像这样:

I want to get an output table where I can see all orders with status 0 and also see all items in the order, like this:

输出

 OrderID      User    Product     Quantity
    1           1        A            2
    1           1        B            1
    2           1        A            3
    2           1        B            1
    2           1        C            1

我尝试过使用不同版本的INNER JOIN等的所有SQL仅获得具有每个OrderID(tblOrder.ID)的第一行.我应该如何编写我的SQL查询以获得我想要的结果?

All SQL I have tried with different versions of INNER JOIN etc only gets the first row with every OrderID (tblOrder.ID). How should I write my SQL query to get the result I want?

接下来，我想用JSON格式的PHP打印输出，以便在另一台设备上运行的C#程序获取结果.最后，应将结果插入类似的类中:

Next I want to print the output with PHP in JSON format for a C# program running on another device to get the result. In the end the result should be inserted in a class like:

Public class orderobject

{
    public int OrderID { get; set; }
    public int UserID { get; set; }
    public String Product[] { get; set; } // Dont know if correct way to make array this way
    public int Quantity[] { get; set; }
}

最终将把结果插入带有此类对象的列表中.因此，列表中的每个条目都是一个单独的订单，其中包含所有所需的订单数据.

The result will finaly be inserted in a list with objects of this class. So every entry in the list is a seperate order with all wanted order data.

我是想错了还是我希望我的SQL最好只获得一个Web请求的方式?我可以在C#代码ant中将结果重组到那里的类中.

Am I thinking wrong or is the way I want my SQL the best to get only one web request? I can reorganize the result in the C# code ant insert it to the class there.

推荐答案

类似于代码编码器

Like Coder of Code mentioned you should JOIN Both the table, apply O.Status = 0 using the WHERE clause and then use ORDER BY to order the results in the required way using ASC or DESC based on the ascending or descending order like below. Its better to go with List<string> as it has methods to search, sort, and manipulate lists.

SELECT OI.OrderID, O.User, OI.Product, OI.Quantity
FROM tblOrder O
INNER JOIN tblOrderItem OI ON O.ID = OI.OrderID
WHERE O.Status = 0
ORDER BY OI.OrderID ASC, OI.Product ASC

这篇关于连接两个表并使用where子句过滤它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！