Can't figure out why the following query behaves like it does, but
I'm guessing there must be something fundamental about how SQL
operates that I'm not comprehending.
I've got a one:many relationship between table A and table B. Table
B records represent "new for XXXX year" for a given record in
table A. For example, there can be 0 or 1 "new for 2000" record in
table B for one record in table A. I've got about 650 records in
table A, with 593 of them with a status of 'ACTIVE'. In table B,
there are about 1000 records, and exactly 89 are for the year 2000.
Table A:
areacode varchar(5)
status varchar(10)
...
Table B:
areacode varchar(5)
year smallint
new varchar(255)
I want to return all of the records in A, along with the "news" for
the year 2000, from table B. I should get 593 records. Since table
B only has year 2000 records for 89 of the records in table A, I
think I need to do a left outer join.
SELECT A.*, B.new
FROM A LEFT JOIN B ON A.areacode = B.areacode
WHERE A.status = 'ACTIVE'
AND B.year = 2000
I get only 89 records returned, and the query appears to behave
exactly like an an inner join. I suspect it has something to do
with my WHERE clause that defines conditions on both of the tables,
but I can't see another way to do this.
Thanks for any assistance,
Jim
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
