I'm sending this attempt w/o attachements. The CF code is inline after
signature. No DB.
Here is one possible solution. It uses a noncorrelated inner query and a
union.
I built a simple access db like you diagrammed (attached)
query and output are contained in .cfm page (attached)
output is as desired returning 2 rows. Those of active status plus the news
for the year 2000 active status (news replaces the 'other' field when
available).
Does this resolve the question?
Richard
_____________ begin cf code
<CFQUERY NAME="news" DATASOURCE="mcatee">
SELECT area,status,other
FROM Table1 T
WHERE '2000' IN
(SELECT year
FROM Table2
WHERE T.ID = ID)
UNION
SELECT area,year,news
FROM Table2
WHERE Table2.year = '2000'
</CFQUERY>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DB Trial</title>
</head>
<body>
<TABLE>
<TR>
<TH WIDTH="65" ALIGN="left">Area</TH>
<TH WIDTH="165" ALIGN="left">Status/year</TH>
<TH WIDTH="275" ALIGN="left">Other/NEWS for the active</TH>
</TR>
<CFOUTPUT QUERY="news"><TR>
<TD WIDTH="65">#news.Area#</TD>
<TD WIDTH="165">#news.Status#</TD>
<TD WIDTH="275">#news.Other#</TD>
</TR>
</CFOUTPUT>
</TABLE>
</body>
</html>
____________ end cf code
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 25, 2000 11:57 AM
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.
