Customers, Orders without subquery

[peterswan]

Hello,

I'm trying to run a query that will get ALL of the customers out of the CUSTOMER table and also to display each order IF the order is greater than a certain date.

I tried a LEFT join but it still requires the customer to have an order of some kind, because of the date restriction in the ORDERS table.

I know I can use a subquery to obtain this information, becuase that's what I'm using now. However, I'm getting a feeling that the subquery is slowing down the performance of the query. Also, it's alot of extra code to write instead of the LEFT join.

When you think about it, there should be a way to return this information with a LEFT join. All I'm asking is to return all customers no matter what, plus any order that is past a certain order date.

I'm hoping there is a way to tell the query to support the LEFT join command AND the restrictions in the WHERE clause as well.

Is this possible?

Thanks,

Peter

 

[SQLSister]

I use a derived table or pull the records to a temp table from the table you want and then do the lef join on the temp table.

something like

select t.field1, a.field2 from table1 t left join
(select field2, field3 from table2 where datefield >'10/01/2006') a 
on t.field1 = a.field3
where a.field2 is null

select field1, field2 into #temp from table2 where datefield >'10/12/2006'

selct field1, field2 from table1 t
left join #temp t2 on t.field1 = t2.field1
where t2.field2 is null

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[r937]

select c.name
     , o.quantity
  from CUSTOMERS as c
left outer
  join ORDERS as o
    on o.customerid = c.id
   [b]and[/b] o.orderdate > '2006-02-28'

r937.com | rudy.ca

 

[peterswan]

All examples worked great.

Thanks, all.

Peter