Well, actually, a former co-worker SQL guru came through for me :)
Here's the query code...
select a.*, b.*
from TableA a,TableB b
where a.Col1 = b.Col1
and b.Col2 =
(select min(c.Col2)
from TableB c
where c.Col1 = a.Col1
)
In this case, "TableA" is the main table, "TableB" is the table with a FK to
TableA's PK.
Col1 is the PK in TableA, Col2 the PK in TableB.
I created a view in SQL7 using this code (obviously edited to reflect real
table/field names) and it worked like a champ.
I couldn't use CF to sort through a query, as I'm using an existing app and
was trying to work within the constraints of that app - i.e. grab a
datasource, tablename, and fieldname to display by, and build a list. I
needed to set this up as a view to use the pre-existing functionality -
otherwise I'd have had to hack some other code for this one situation, which
I didn't want to do - messy.
-----Original Message-----
From: Dean H. Saxe [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 20, 2001 12:15 PM
To: SQL
Subject: Re: SQL7 View - trying to pull only certain records...
Scott,
Good question, however, unless you can distinguish record #1, 2, 3...N
there is no way to do this that I know of in a single query. However, for
each ID value you can pull the first record from the view (which will
contain the full join result set) using the syntax SELECT TOP 1... but as I
said, you have to repeat the query for each record.
If you are using CF to process the results, retrieve the entire resultset,
order by ID and then in the CFOUTPUT group by ID, that way you can retrieve
only the data in the first record each time through the resultset.
-dhs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
