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
At 10:01 AM 6/20/01 -0600, you wrote:
>Hey gang...
>
>First attempt at doing a view in SQL7, and I've got a semi-odd need.
>
>I have two tables - joined by an ID. Table one has a single record, table
>two has multiple records tied to the first table's single record. I want to
>get my view set up so it pulls the common ID, and ONLY the first of multiple
>records in the second table.
>
>The code the view designer generates is:
>
>SELECT N.NewsletterID, NC.NewsletterTitle
>FROM TNewsletter N INNER JOIN
> TNewsletterContent NC ON N.NewsletterID = NC.NewsletterID
>
>Right now I've got two records in "TNewsletter", and four in
>"TNewsletterContent" - two for each of the two records in the first table.
>The code above returns four records - I want to get it to return just two.
>
>Thoughts?
>
>Thanks in advance -
>--Scott
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
