February 5, 2004 at 4:19 pm
I'm playing around with T-SQL. The task I have given myself is TO FIND ALL CUSTOMERS WHO HAVE PLACED AN ORDER WITH A QUANTITY < 5.
I first write it as a SubQuery that returns 70 records.
I then write it as a join and it returns 170 records.
I'm trying to work out why the different result set. Is there soemthing wrong with my queries below?
SELECT C.*
FROM Customers C
WHERE CustomerID IN (SELECT CustomerID FROM Orders O
INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
WHERE OD.Quantity < 5)
SELECT C.*
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
WHERE OD.Quantity < 5
February 5, 2004 at 5:26 pm
The first query show customer once only who meets the your condition but second will show same customer many times if the customer made more than one orders and quantity less than 5 of each orders.
February 5, 2004 at 5:33 pm
February 5, 2004 at 5:42 pm
Use distinct.
SELECT distinct C.*
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
WHERE OD.Quantity < 5
February 5, 2004 at 5:46 pm
February 6, 2004 at 12:29 am
As I doubt you'll find a book especially on subquery, I would have a look at
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 8, 2004 at 5:55 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply