SQL Server Trying to consolidate 2 SQL queries into 1 SQL for better efficiency (if it's possible)

w9apscun 于 2023-04-19 发布在其他

关注(0)|答案(2)|浏览(121)

I have a SQL question. I have a SQL Server database with 2 tables I want to grab data from. It's kind of similar to the database found here:

https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

Just the Customers and Orders table are relevant.

What I'd like to do is use a SQL query to list all of the customers, and below each customer, display all their respective orders.

Currently I'm doing something like this (in my classic ASP web page):

SELECT * FROM Customers ORDER BY CustomerName;

I'm then iterating through all the customers (returned records), and displaying them...but for each record/customer returned, I run this:

SELECT * FROM Orders WHERE CustID = " & rs("id")

Then iterate through those results to display all the open orders for that customer.

Basically it's 1 long page that shows all our 'customers' and each of their open 'orders'.

It's become fairly large/long, and is taking a bit longer than I'd like to load these days.

So again, I'm guessing I could do this more efficiently with a single SQL query to eliminate that 2nd trip to the database for each customer record?

I know didn't give a ton of detail here, but hopefully it makes sense. I didn't want to create a super long post/thread. I can definitely give more details if needed.

Thank you for reading!

sql-server

来源：https://stackoverflow.com/questions/76047161/trying-to-consolidate-2-sql-queries-into-1-sql-for-better-efficiency-if-its-po

2条答案

按热度按时间

eagi6jfj1#

In order to avoid retrieving the customer data with each of their orders again and again, you can just run two queries, both ordered by customer name:

Customers

SELECT * FROM customers ORDER BY customername;

Orders

SELECT o.* 
FROM orders o
JOIN customers c ON c.custid = o.custid
ORDER BY c.customername;

赞(0）回复(0）举报 2023-04-19

e5nqia272#

seems you need simple join

SELECT c.*, o.*
    FROM Customers c  
    INNER JOIN Orders   o on  o.CustID  = c.id
    ORDER BY c.CustomerName;

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server Trying to consolidate 2 SQL queries into 1 SQL for better efficiency (if it's possible)

2条答案

Customers

Orders

相关问题

热门标签

最新问答