Board index » delphi » Why isn't outer join giving me complete list

2003-10-31 03:38:54 AM
delphi171
Hi all,
I have a query that tries to find all items in A that are missing in
B. The intent was to filter the resulting dataset for field = 0 that
would indicate it was missing from B and then print the results. But
it fails if I try to use a where clause accessing a field in B.
Originally, I tried ...
select v.tapeID, v.ttl, v.sts, count(d.TapeID) as Viewed
from "VLIB.DB" V LEFT OUTER JOIN "VIEWD.DB" D
on (v.tapeID = d.tapeID)
/* where (d.dateViewed>= "1/1/2001") */
group by v.tapeID, v.ttl, v.sts
That gives me a complete list of VLib, with appropriate zeroes where
no entries were found from ViewD. i.e.
----------------------
1 ChapOne F 2
2 ChapTwo E 0
3 ChapThree F 14
----------------------
When I add the where clause, line two disappears and the count of
lines one and three are respectively correct at 1 and 6.
Ultimately, I have kludged around the problem by using a plain query
select v.tapeID, v.ttl, v.sts, count(d.TapeID) as Viewed
from "VLIB.DB" V,"VIEWD.DB" D
where (v.tapeID = d.tapeID) and (d.dateViewed>= "1/1/2001")
group by v.tapeID, v.ttl, v.sts
I then scanned VLib, searched for the corresponding entry in the
query and if it failed, made an appropriate entry in a temporary
table. I then reported off that temporary table.
What am I missing?
Thanks, GM
==========================================
When replying via email please move all
numbers from right of at symbol to left of
at symbol to get email address.
 

Gary Mugford writes:
dissappear? If so then try:
where (d.dateViewed is null or d.dateViewed>= '1/1/2001')
Small detail: you should use *single* quotes when specifying values such as
the above date.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

"Gary Mugford" <XXXX@XXXXX.COM>writes:
SELECT A.* FROM TableA A
WHERE A.KeyField NOT IN (SELECT B.KeyField FROM TableB B)
Bye,
Marcio
 

Wayne,
Your suggestion worked. Thank you. I am still confused as to why
the original clause prevents a full left join, since I thought
inclusion even with nulls was the raison d'etre of the left join, but
I'll live with the uncertainity.
Regards, GM
On Thu, 30 Oct 2003 19:09:25 -0500, "Wayne Niddery [TeamB]"
<XXXX@XXXXX.COM>writes:
When replying via email please move all
numbers from right of at symbol to left of
at symbol to get email address.
 

Marcio,
Thanks for the reply. While it answered a different question that I
had too, I opted to use another suggestion from Wayne Niddery to solve
the immediate problem. Your compound query is quite interesting.
Regards, GM
On Fri, 31 Oct 2003 01:32:44 -0200, "Marcio Ehrlich"
<XXXX@XXXXX.COM>writes:
When replying via email please move all
numbers from right of at symbol to left of
at symbol to get email address.
 

Gary Mugford writes:
will still be returned - however as soon as you introduce a condition on the
right table, that condition must be observed by the query and effectively
turns the left join into an inner join.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson