hmmm...so, this doesn't work? <grin> I know you said it didn't earlier,
but...
Select Area, Year, News
from Areas A LEFT OUTER JOIN AreaNews AN
ON AN.area = A.area
where A.Status = 'ACTIVE'
and AN.Year = 2000
What results do you get from this query?
Also, are you able to create a join which displays all the data? From there
you should be able to start adding WHERE clause items.
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 25, 2000 12:57 PM
To: CF-Talk
Subject: Re: SQL Help
Richard,
Thank you for your help. Maybe if I give a much simpler example, you'll
get an idea of what I'm after.
TABLE1 (Areas) TABLE2(AreaNews)
============================ ======================
Area Status (OtherStuff) Area Year News
---- -------- ------------ ---- ---- ---------
co01 ACTIVE xxxxxx co01 1998 xxxxx
co01 1999 xxx
co01 2000 xxxxxx
co02 INACTIVE xxxxxxxxxx co02 1997 xx
co02 1998 xxxxx
co03 ACTIVE xxxxxxxx co03 1998 xxxxxxx
co03 1999 xxxx
So, in the above example, TABLE1 has 3 records, TABLE2 has 8 records, I
want (in english): "All the active areas, along with any news for the
year 2000". The query should return 2 rows, only the first of which
would have news for 2000. Note that I'm selecting some other fields
from TABLE1, but just News from TABLE2.
Thanks,
Jim
-----Original Message-----
From: Richard Kern <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Date: Monday, September 25, 2000 11:12 AM
Subject: RE: SQL Help
>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.
----------------------------------------------------------------------------
--
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.
