Parent - Child

I am using ASP/VB and MS SQL 2000
I have a recordset that I filter out, it has an ID, this table is called
'persons'
Then I have another table called 'children', this table has its own id's but
it also has the ID from 'persons'
So that one record from 'persons' could have one, none or multiple records
from 'children' which are linked by the ID of the person.
This is table persons:
ID - Autonumber
Name - nvarchar (Here goes the name of the person)
This is the children table:
ChildId - Autonumber
PersonId - ID from the persons table
Name - Name of the child
How can I return a query that will display one row ONLY for the 'person' and
then all the rows for the child that are linked to the 'person', could be
none or multiple rows.
I could not do this with a left join because if I did it would display the
name of the person several times.
I hope I explained myself.
A tutorial would be great.
Thanks,
AleksBetter to do this in your client app / reporting tool / programming language
.
AMB
"Aleks" wrote:

> I am using ASP/VB and MS SQL 2000
> I have a recordset that I filter out, it has an ID, this table is called
> 'persons'
> Then I have another table called 'children', this table has its own id's b
ut
> it also has the ID from 'persons'
> So that one record from 'persons' could have one, none or multiple records
> from 'children' which are linked by the ID of the person.
> This is table persons:
> ID - Autonumber
> Name - nvarchar (Here goes the name of the person)
> This is the children table:
> ChildId - Autonumber
> PersonId - ID from the persons table
> Name - Name of the child
> How can I return a query that will display one row ONLY for the 'person' a
nd
> then all the rows for the child that are linked to the 'person', could be
> none or multiple rows.
> I could not do this with a left join because if I did it would display the
> name of the person several times.
> I hope I explained myself.
> A tutorial would be great.
> Thanks,
> Aleks
>
>|||Try this:
select 'Person' as Source
, Person.Id as PersonId
, NULL as ChildId
, Person.Name
from Person
UNION ALL
select 'Child' as Source
, Child.PersonId as PersonId
, ChildId as ChildId
, Child.Name
from Child
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!