Try it again, with a subject this time...
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/[email protected]/
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.
