Selection of two tables in sql

i post this question because i really can’t find a solution, i spent a day searching how to do this but i can’t make it work. that’s also because i’m new to sql and i need some help.

Situation :I have a database with 3 tables : Orders, Producs, Buys. In my C#/.NET application i need to display all products from one order, first here is the tables structure.

TABLE orders
    int id
    int Totalprice

Table products
    int id
    string name
    ...

Table buys
    int id
    int price
    int quantity
    int orderId
    int productId

Also, a buy can have many products and have 1 order, and an order can have many buys.

What i’m doing in my application :

In my C#/.NET application, this is what i do :

SELECT * FROM buys WHERE orderid=listview.selecteditem.orderid;

i store this results in a list and after this:

foreach(item in buys)//buys is the list stored from the previous query
{
        SELECT * FROM products WHERE productid = item.productId;
}

NOTE :this is only a pseudo code, because i didn’t start coding yet.

The problem :this code is working for me, but it’s a bit slow and need to perform two queries to get the expected result. i need a way to do this in one sql query. Thanks in advance

You may want to use
join

:

SELECT *
FROM buys b
JOIN orders o
ON o.id = b.orderId
JOIN products p
ON b.id = o.productId

Note also that is good practice to specify the fields instead of using *
.

As Michael suggested, you may want also to look at indexes
for better query performance.

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Selection of two tables in sql

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录