using Left outer Join in MS sql

Isabell
Isabell used Ask the Experts™
on
I have the following query:
with orders as(
	select SalesOrderID, CustomerID, OrderDate
	from Sales.SalesOrderHeader
	where OrderDate = '2005/07/01'
)
select c.CustomerID, orders.SalesOrderID, orders.OrderDate
from Sales.Customer as c
left outer join orders on c.CustomerID = orders.CustomerID
order by OrderDate desc

Open in new window


Can we achieve the same thing using just Left outer join without using CTE?
I did something like below, but it didn't work well.  I thought that the following query gets all the customers regardless if he/she placed an order on 2005/07/01.
select c.CustomerID, h.SalesOrderID, h.OrderDate
from Sales.Customer as c
left outer join Sales.SalesOrderHeader h
on c.CustomerID = h.CustomerID
where OrderDate = '2005/07/01'
order by OrderDate desc

Open in new window


Can you please explain what I did wrong ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
No. the where clause of the second query creates the same conditions as an inner join.
i.e. the left join gets overruled by the where clause

Try this instead, using AND on the left join instead of the where clause

SELECT
    c.CustomerID
  , h.SalesOrderID
  , h.OrderDate
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader h ON c.CustomerID = h.CustomerID
                 AND OrderDate = '2005/07/01'
ORDER BY
    OrderDate DESC

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try this..
select c.CustomerID, orders.SalesOrderID, orders.OrderDate
from Sales.Customer as c
left outer join (
select SalesOrderID, CustomerID, OrderDate
	from Sales.SalesOrderHeader
	where OrderDate = '2005/07/01') orders on c.CustomerID = orders.CustomerID
order by OrderDate desc

Open in new window


Try this as well..
select c.CustomerID, orders.SalesOrderID, orders.OrderDate
from Sales.Customer as c
left outer join Sales.SalesOrderHeader orders on c.CustomerID = orders.CustomerID AND OrderDate = '2005/07/01'
order by OrderDate desc

Open in new window

Distinguished Expert 2017

Commented:
Without knowing your table relationship, it is possible that you can use the sales query (cte) and work your way back to get the customer to whom these apply.
Are you looking to get sales.customer who only gave orders from the specific date?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
The first query returns just the rows of sales that meet the date condition, that result is then left joined to customers, so all customers get listed.
+----------+
|  Sales   |  
+----------+------------+      | Customer |
| Customer | Date       |      +----------+
| A        | 2002-02-01 |      | A        |
| B        | 2005-07-01 | >>>> | B        | B | 2005-07-01 | Left Joined
| B        | 2005-07-02 |      | C        | 
| C        | 2007-01-03 |
| C        | 2007-01-04 |
| C        | 2007-01-05 | 
+----------+------------+

Open in new window


The second query requires that every row MUST meet the date condition, so only those customers that have a sale on that date will be listed.

+----------+
|  Sales   |  
+----------+------------+      | Customer |
| Customer | Date       |      +----------+
| A        | 2002-02-01 |                                    EVERY ROW MUST
| B        | 2005-07-01 | >>>> | B        | B | 2005-07-01 | HAVE this date
| B        | 2005-07-02 |                                    due to where clause
| C        | 2007-01-03 |                                    
| C        | 2007-01-04 |
| C        | 2007-01-05 | 
+----------+------------+

Open in new window

Author

Commented:
Thank you so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial