Is it possible to return all parent rows but only first child row of each parent for a t-sql join?

microsoft.public.sqlserver.programming

8 Sep 2006 8:13 PM

Jia

Have two tables order and orderline.
Is there a way in T-sql to get an inner join to return all order records but
only the first orderline record of each order?

Sort of like:

Select * from order inner join top 1 orderline on <join condition>.....

Thanks.

8 Sep 2006 8:23 PM

David Portas

Jia wrote:

> Have two tables order and orderline.
> Is there a way in T-sql to get an inner join to return all order records but
> only the first orderline record of each order?
>
>
> Sort of like:
>
> Select * from order inner join top 1 orderline on <join condition>.....
>
> Thanks.

Tables have no inherent order so there is no "first". You need to
define the row you want using some column in the table. Here's an
example from the Pubs database that gives one title per author (the
MIN(title_id) for each author):

SELECT a.au_id, a.au_lname, a.au_fname, t.title
FROM authors AS a
JOIN
(SELECT au_id, MIN(title_id) AS title_id
FROM titleauthor
GROUP BY au_id) AS u
ON a.au_id = u.au_id
JOIN titles AS t
ON u.title_id = t.title_id ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--