Posted by KoliPoki on 03/13/07 22:02

On Mar 9, 8:43 am, "Steve London" <sylon...@optonline.net> wrote:
> I'm sure this has been brought up many times, but I will ask anyway.
>
> Let's say I have 2 tables related:
>
> Owner:
> ---------
> o_id
> o_name
>
> Dog:
> ---------
> d_id
> d_name
> o_id - for Owner table.
>
> If the data is laid out as
>
> o_id o_name
> 1 John
>
> d_id d_name o_id
> 1 Skippy 1
> 2 Fido 1
>
> How can I make a query that will produce the following results:
>
> o_id o_name owned dog names
> 1 John Skippy, Fido
>
> I think it has something to do with unions but I can't seem to get it. I'm
> using SQL Server Compact Edition.


CREATE TABLE Owners -- plural if you have more than one
(owner_id int NOT NULL PRIMARY KEY,
owner_name varCHAR(20) NOT NULL);

CREATE TABLE Dogs -- plural if you have more than one
(dog_id int NOT NULL PRIMARY KEY,
dog_name varCHAR(20) NOT NULL,
dog_owner_id int
);

insert into Owners (owner_id,owner_name) values(1,'John')
insert into Dogs (dog_id,dog_name,dog_owner_id) values(1,'Skippy',1)
insert into Dogs (dog_id,dog_name,dog_owner_id) values(2,'Fiddo',1)

declare @s varchar(100) select @s = coalesce(@s +', ','') + d.dog_name
from dogs d inner join owners o on d.dog_owner_id = o.owner_id where
o.owner_id = 1; select owner_id, owner_name, @s from owners where
owner_id = 1

[Back to original message]