Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query Help

Posted on 2011-09-24
2
Medium Priority
?
295 Views
Last Modified: 2012-05-12
I'm trying to return only 1 row for each order.
An order can have multiple line items.

I want to check each line item to make sure the quantity in stock is greater than 0.
Right now, there is an order that has two lines and both are in stock, but I just want it to return one row.
I pretty much just need a couple columns from the orders table and not the line items table.

SELECT
      *
FROM Orders a
INNER JOIN LineItems b
ON a.OrderID = b.OrderID
INNER JOIN Products c ON c.ProductID = b.ProductID
WHERE
      c.QuantityInStock > 0
AND
      a.OrderStatusID = 1
AND
      a.GatewaySuccessful = 1
AND
      a.ToBePrinted = 1
0
Comment
Question by:JRockFL
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36592383
Then try something like this:

SELECT
      a.ColumnX, a.ColumnY, a.ColumnZ
FROM Orders a
INNER JOIN LineItems b
ON a.OrderID = b.OrderID 
INNER JOIN Products c ON c.ProductID = b.ProductID
WHERE
      c.QuantityInStock > 0
AND 
      a.OrderStatusID = 1
AND 
      a.GatewaySuccessful = 1
AND 
      a.ToBePrinted = 1
GROUP BY
      a.ColumnX, a.ColumnY, a.ColumnZ

Open in new window

0
 
LVL 8

Author Closing Comment

by:JRockFL
ID: 36592395
Awesome, thank you
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question