Silly question but have you tried a right outer join?
SELECT A.*, B.new
FROM A RIGHT JOIN B ON A.areacode = B.areacode
WHERE A.status = 'ACTIVE'
AND B.year = 2000
--mark
--------------------------------------------------------------
Mark Warrick
Phone: (714) 547-5386
Efax.com Fax: (801) 730-7289
Personal Email: [EMAIL PROTECTED]
Personal URL: http://www.warrick.net
Business Email: [EMAIL PROTECTED]
Business URL: http://www.fusioneers.com
ICQ: 346566
--------------------------------------------------------------
> -----Original Message-----
> From: Jim McAtee [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, September 24, 2000 3:10 PM
> To: CF-Talk
> Subject: SQL Help
>
>
> 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/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.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message
to [EMAIL PROTECTED] with 'unsubscribe' in the body.
