The post is not entirely clear, You imply wanting 682 records (all A 'along
with "news"')
but specify only 593. Selecting only 'actives' in A will get 593 as you
desire. But, I will
consider that you want records from A and B that meet your criteria. In
that case 89 records
meet 'active' and 'year = 2000'. So the query apparently works. However if
you want 682 (
all A and those in B that meet the criteria) look at a 'union'.
select_statement
WHERE
UNION
select_statement
WHERE
ORDER BY (only one order clause allowed)
(quick review of some SQL)
1. an outer join shows the join plus all the records that did not
qualify from the first named table in your join specification.
2. A right join will do the same for the records in the second table.
3. non matching rows in either table can also be specified from either
table (left or right join)
4. a union combines data from multiple queries into one display and by
default
removes duplicates (but check your documentation) UNION ALL will will
include
the duplicates
5. depending on your SQl implementation the join may be specified in the
WHERE (rather than the FROM) clause using
*= (include all rows from first table) or =* (include all rows in second
table)
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Sunday, September 24, 2000 4: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/[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.
------------------------------------------------------------------------------
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.
