?Hi All,

Just trying to confirm if my expectations of a left outer join query are
correct or if something strange is going on.  I have a customer and invoice
table that are joined by indexes on the customer number field that is
present in each table.  There are 3 records in the customer table (number 0,
1, and 2), and there are two invoices (one for customer 0 and one for
customer 1).

running the following query

select c.custnum, i.custnum from cust c
 left outer join invoice i on c.custnum = i.custnum

results with the following being returned

0, 0
1, 1
2, null

If I modify the query to restrict the records and add a where clause that
excludes all of the records in the invoice table

select c.custnum, i.custnum from cust c
 left outer join invoice i on c.custnum = i.custnum
where i.transdate > date '2999-01-01'

I get no records back at all.  Since I'm using a left outer join, I would
expect to get all records back from the customer table even if there are no
results in the invoice table.  Are my expectations of what should be
returned incorrect?


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.atrex.com
---------------------------------------  

Terry,

Both queries are returning the expected result, as far as I can see.
The first one return the row (2, null) because by definition, a left outer join always includes in the result set all rows in the left table, except if they don't match the condition of the WHERE clause, and that's what is going on in the second query.  No rows verify the where condition, so they have to be excluded from the result set, as expected.

--
Fernando Dias
[Team Elevate]

?Fernando ,

> No rows verify the where condition, so they have to be excluded from the
> result set, as expected.

Thanks for the clarification.  Makes sense now that I'm looking at it from
the proper perspective.


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.atrex.com
---------------------------------------  

If you want to return customers with no invoices, you can put the date check as part of your join condition

select c.custnum, i.custnum from cust c
left outer join invoice i on c.custnum = i.custnum And i.transdate > date '2999-01-01'

That way you'll get all customer records, but no invioces on and before '2999-01-01'

?
> If you want to return customers with no invoices, you can put the date
> check as part of your join condition

Thanks Chris.  Now I just have to figure out a way to do this with
ReportBuilder.

--
---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com

Now shipping Atrex 13. For more information go to
http://www.atrex.com/news.asp

Atrex Electronic Support Options:
Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
Email: support@atrex.com
Newsgroup: news://news.1000years.com/millennium.atrex
Fax: 1-925-829-1851
Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
---------------------------------------  

>>Thanks Chris.  Now I just have to figure out a way to do this with ReportBuilder.

Hi Terry,

How did you get on with ReportBuilder?  It took me some time to work it out the first time - but you are probably smarter than me.  Please let me know if you want to know how I do similar tasks.

Richard Harding
rharding@wck.com.au

??Richard,

> How did you get on with ReportBuilder?  It took me some time to work it
> out the first time - but you are probably smarter than me.  Please let me
> know if you want to know how I do similar tasks.

I've done pretty well with ReportBuilder.  Been using it since probably
version 8 and have my own abstraction class around it so that I don't have
to do the same things over and over and over again.  It doesn't look like
you can force RB to use a variable in a join between tables, but this does
work as expected if I use two separate data views and let RB do the join
between the two result sets.  So I at least have a solution from the RB side
of things if I need it.

Oh, and I'm not so sure about that "smarter" thing.  I've been doing more
than my fair share of Duh moments lately.

--
---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com

Now shipping Atrex 13. For more information go to
http://www.atrex.com/news.asp

Atrex Electronic Support Options:
Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
Email: support@atrex.com
Newsgroup: news://news.1000years.com/millennium.atrex
Fax: 1-925-829-1851
Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
---------------------------------------