Force Uniqueness on one column

HI:
If when joining parent and child tables, a query returns multiple entries
for a given parent, how can I limit query to showing only first child? Kind
of like grouping on one field in result set.
Thanks,
CharlieDefine "first".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
> HI:
> If when joining parent and child tables, a query returns multiple entries
> for a given parent, how can I limit query to showing only first child?
> Kind
> of like grouping on one field in result set.
> Thanks,
> Charlie
>|||Hi Tom, let me restate..
If query joins a parent table with a child table in a one-to-many relation
the results set will show the parent id repeating for each child. I want
the query to show only one child despite having many. How do I filter join
to limit result set to only one child per parent even though it a parent may
have many child records.
Thanks,
charlie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> Define "first".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:eIegtNg6FHA.632@.TK2MSFTNGP10.phx.gbl...
entries
>|||SELECT
Parent.ID
, Child.ID
, Child.Data
FROM
Parent
INNER JOIN
(
SELECT
Child.Parent_ID
, Child.ID
, Child.Data
FROM
Child
INNER JOIN
(
SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
Parent_ID
) LowestChildForParent
ON
Child.Parent_ID = LowestChildForParent.Parent_ID
AND
Child.ID = LowestChildForParent.ID
) Child
ON
Parent.ID = Child.Parent_ID
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
join
> to limit result set to only one child per parent even though it a parent
may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Again, define "first". You haven't posted your DDL. We have no idea which
of the child rows is the "first" for a given parent ID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Tom, let me restate..
> If query joins a parent table with a child table in a one-to-many relation
> the results set will show the parent id repeating for each child. I want
> the query to show only one child despite having many. How do I filter
> join
> to limit result set to only one child per parent even though it a parent
> may
> have many child records.
> Thanks,
> charlie
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:unojjUg6FHA.636@.TK2MSFTNGP10.phx.gbl...
> entries
>|||Using min() or max() value for a set of keys in grouping should work. This
will first or last child.
Thanks
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437a10a3$0$133$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT
> Parent.ID
> , Child.ID
> , Child.Data
> FROM
> Parent
> INNER JOIN
> (
> SELECT
> Child.Parent_ID
> , Child.ID
> , Child.Data
> FROM
> Child
> INNER JOIN
> (
> SELECT Parent_ID , MIN( ID ) AS ID FROM Child GROUP BY
> Parent_ID
> ) LowestChildForParent
> ON
> Child.Parent_ID = LowestChildForParent.Parent_ID
> AND
> Child.ID = LowestChildForParent.ID
> ) Child
> ON
> Parent.ID = Child.Parent_ID
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ucEjkag6FHA.744@.TK2MSFTNGP10.phx.gbl...
relation
want
> join
> may
child?
>