Returning only one row from joined table

Hello
Given two tables in a one-to-many relationship, how do I write a query to
return only one row from the table on the "many" side of the relationship?
e.g., in the "pubs" sample database, the following query returns all rows
from the Authors table and all related rows from the TitleAuthor table:
SELECT
A.au_id,
A.au_lname,
A.au_fname,
TA.title_id,
TA.royaltyper
FROM
Authors A
LEFT OUTER JOIN TitleAuthor TA ON TA.au_id = A.au_id
ORDER BY
A.au_id,
TA.title_id
In my case, I want all rows from the Authors table and for related rows in
the TitleAuthor table, only the first row (ordered by title_id).
Thanks!!!
AlexSELECT
A.au_id,
A.au_lname,
A.au_fname,
TA.title_id,
TA.royaltyper
FROM Authors A
LEFT JOIN
(SELECT au_id, MIN(title_id) AS title_id
FROM TitleAuthor
GROUP BY au_id) AS T
ON T.au_id = A.au_id
LEFT JOIN
TitleAuthor TA
ON TA.au_id = T.au_id
AND TA.title_id = T.title_id
ORDER BY
A.au_id,
TA.title_id ;
David Portas
SQL Server MVP
--|||Select TOP1 attribute_list
From table
etc...
Alex wrote:
> Hello
> Given two tables in a one-to-many relationship, how do I write a query to
> return only one row from the table on the "many" side of the relationship?
> e.g., in the "pubs" sample database, the following query returns all rows
> from the Authors table and all related rows from the TitleAuthor table:
> SELECT
> A.au_id,
> A.au_lname,
> A.au_fname,
> TA.title_id,
> TA.royaltyper
> FROM
> Authors A
> LEFT OUTER JOIN TitleAuthor TA ON TA.au_id = A.au_id
> ORDER BY
> A.au_id,
> TA.title_id
> In my case, I want all rows from the Authors table and for related rows in
> the TitleAuthor table, only the first row (ordered by title_id).
> Thanks!!!
> Alex
>