join table

[mac555]

Let's say there are Customers Table and Orders Table. There are several customer types. I want to get the rows from 2 tables with the following conditions: All customers rows with of particular customer types (ex. 1 and 2) and their associated orders for a specified date range. If I used this statement " Select cust.field1, cust.field2, ord.* from customers cust left join orders ord on cust.id=ord.id where (cust.type=1 or cust.type=2) and ord.date>='01-01-2010' " then it will not include all the customers without order records. If I leave out the date condition then I will get the customers rows with no order records but then it will include too many order records outside the date range. How (is it possible) do I accomplish this in one statement ?

 

[gmmastros]

Change the keyword "where" to "and". Trust me. Try it. If it works for you, then do a google search on "left join acts like and inner join".

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Funny, I wrote a blog today on this exact problem. Will publish it soon.

PluralSight Learning Library

 

[markros]

Take a look at this blog post Why LEFT JOIN doesn't select records

PluralSight Learning Library

 

[mac555]

Changed "Where" to "and" didn't quite work but
I changed the statement to "Select cust.field1, cust.field2, ord.* from customers cust left join orders ord on cust.id=ord.id and ord.date>='01-01-2010' where (cust.type=1 or cust.type=2)" worked.
Now I need to figure out how to get the same query result in Crystal report. Anyone have some tips ?

Thanks everyone for the the help.