I'm looking for help with a SQL query to pull orders from our database with a particular status.


Let me preface this by stating that I'm new to SQL queries, and am learning on the fly.  This type of question has been asked and answered multiple times, but I think my lack of expertise is keeping my from applying it properly to my environment.

I have a system with sales data kept in two separate SQL tables; salestable contains the status of the sales orders, while salesline has the detailed information from each order.  'SalesID' is common to both tables.

I need a query that will give the line item detail for every Sales Order with a particular status.  I don't need to filter the results in the query (I can return all the columns), as we can do that afterward in Excel if need be.

For example:

dbo.SalesTable contains the SalesID, CustomerName, PurchaseOrderRef, and most importantly, SalesStatus (gives the status of the order as picked, packed for shipping, shipped, etc.).

dbo.SalesLines contains the SalesID, CustomerName (called CustAccount), ItemID, ShippingDateRequested, and ShippingDateConfirmed.

My query needs to return all information above for each order (identified by SalesID) that has a SalesStatus of 1 or 3.

I assume that this requires a join of some sort, but I'm struggling a bit with the syntax.  Any help would be appreciated!


Scott MilnerApplication AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
FROM SalesTable T
    INNER JOIN SalesLines L ON T.SalesID = L.SalesID
WHERE T.SalesStatus IN (1,3)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The QBE is a huge help when you are just learning.  It is even a big help if you know how to write SQL.  To create your query:
1. Open the QBE
2. Select the two tables.
3. If you have defined a relationship (and you should always do this), Access will automatically draw the join line as it is in the relationship window.  If Access doesn't draw the join line, you need to do it.  Click on the join field in one table and drag to the other table.  If the names are the same, that is all you have to do.  If the names are different, you may have to adjust the column name in the dialog box.
4. Select ONLY the columns you need from each table.
5. In the Criteria line for SalesStatus enter -
1 or 3
5a. I prefer the In() syntax shown by John so you could enter -
IN (1,3)
6. Name the query appropriately and save it.
Scott MilnerApplication AdministratorAuthor Commented:
@Vitor, thank you very much.  Your query worked perfectly.

@PatHartman, thank you for your input.  I'm not familiar with the QBE, so I'm going to do some research this weekend to better understand your response.  I'll probably post back a follow-up question or two on Monday.

thanks again to you both, and have a great weekend!
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
smilner, do you need more help on this question?
Scott MilnerApplication AdministratorAuthor Commented:
Good morning, Vitor.

Thanks for the follow-up.  I was busy this weekend, and didn't get back to the question like I'd hoped.

The only follow-up question I'd have for you is to ask if you could explain the syntax of your statement.  Specifically, what does the capital T and L do in the statement?

Thank you... the query is working perfectly.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The select is one of the more basics that you can have in SQL. It returns all records (SELECT *) that sales has details (SalesID = SalesID) and where the status matches 1 and 3 (SalesStatus IN (1,3)).
The 'T' and 'L' are aliases respectively for SalesTable and SalesLines, so you won't need to write the whole table names again, otherwise the select command would be:
FROM SalesTable
    INNER JOIN SalesLines ON SalesTable.SalesID = SalesLines.SalesID
WHERE SalesTable.SalesStatus IN (1,3)

Open in new window

Scott MilnerApplication AdministratorAuthor Commented:
I see... so the 'SalesTable T' portion of the FROM statement just sets T as an alias?  

Scott MilnerApplication AdministratorAuthor Commented:
The query worked perfectly... thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.