Returning Just one row per parent

I have two tables that have a one to many relationship (Story and Photo
tables). There are many Photos per Story. I'm trying to create a SQL
Statement that will return just one row for each story, that also returns th
e
column SlidePath from the Photo table as the third column returned.
Here is what I have so far:
DECLARE @.DestinationID int
SET @.DestinationID = 1
SELECT S.StoryGUID,
S.[Name] as StoryName,
(SELECT TOP 1 SlidePath
FROM Photo P INNER JOIN Story S ON P.StoryID = S.StoryID
WHERE S.DestinationID = @.DestinationID) AS 'SlidePath'
FROM Story S
WHERE S.DestinationID = @.DestinationID
The problem is that SlidePath column doesn't match the story, it just
returns the first story that matches the DestinationID. As you can tell ther
e
is a third table called Destination that has a one to many relationship with
Story. One destination has many stories.
Thanks.You're going to have to be more specific. Which one photo do you want? SQL
Server isn't going to just pick an arbitrary photo, and there is no such
thing as ANY() in SQL Server (though a lot of people seem to think there
should be). Can you give more specific requirements by including DDL,
sample data, and desired results? Please see http://www.aspfaq.com/5006
On 2/27/05 6:11 PM, in article
36D9DC7A-83EC-4DA3-9211-E5393379CEE4@.microsoft.com, "jmhmaine"
<jmh@.online.nospam> wrote:

> I have two tables that have a one to many relationship (Story and Photo
> tables). There are many Photos per Story. I'm trying to create a SQL
> Statement that will return just one row for each story, that also returns
the
> column SlidePath from the Photo table as the third column returned.
> Here is what I have so far:
> DECLARE @.DestinationID int
> SET @.DestinationID = 1
> SELECT S.StoryGUID,
> S.[Name] as StoryName,
> (SELECT TOP 1 SlidePath
> FROM Photo P INNER JOIN Story S ON P.StoryID = S.StoryID
> WHERE S.DestinationID = @.DestinationID) AS 'SlidePath'
> FROM Story S
> WHERE S.DestinationID = @.DestinationID
> The problem is that SlidePath column doesn't match the story, it just
> returns the first story that matches the DestinationID. As you can tell th
ere
> is a third table called Destination that has a one to many relationship wi
th
> Story. One destination has many stories.
> Thanks.|||Using TOP 1 works, so it will return the first photo.
Here is the build DDL on the two tables:
CREATE TABLE [Story] (
[StoryID] [int] IDENTITY (1000, 1) NOT NULL ,
[StoryGUID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_Story_StoryGUID] DEFAULT (newid()),
[Name] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DestinationID] [int] NOT NULL ,
CONSTRAINT [PK_Story] PRIMARY KEY CLUSTERED
(
[StoryID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [Photo] (
[PhotoID] [int] IDENTITY (1000, 1) NOT NULL ,
[SlidePath] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ThumbnailPath] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Caption] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[StoryID] [int] NOT NULL ,
CONSTRAINT [PK_Photo] PRIMARY KEY CLUSTERED
(
[PhotoID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Photo_Story] FOREIGN KEY
(
[StoryID]
) REFERENCES [Story] (
[StoryID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
My goal is to have a single row for each Story that matches DestinationID,
the first two columns are from story, the third is the SlidePath column from
photo. Thanks.
"Aaron [SQL Server MVP]" wrote:

> You're going to have to be more specific. Which one photo do you want? S
QL
> Server isn't going to just pick an arbitrary photo, and there is no such
> thing as ANY() in SQL Server (though a lot of people seem to think there
> should be). Can you give more specific requirements by including DDL,
> sample data, and desired results? Please see http://www.aspfaq.com/5006
>
>
> On 2/27/05 6:11 PM, in article
> 36D9DC7A-83EC-4DA3-9211-E5393379CEE4@.microsoft.com, "jmhmaine"
> <jmh@.online.nospam> wrote:
>
>|||sample data, and desired results?
On 2/28/05 7:09 AM, in article
DA0302E5-86CD-4025-B414-903870B776B5@.microsoft.com, "jmhmaine"
<jmh@.online.nospam> wrote:

> Using TOP 1 works, so it will return the first photo.
> Here is the build DDL on the two tables:
> CREATE TABLE [Story] (
> [StoryID] [int] IDENTITY (1000, 1) NOT NULL ,
> [StoryGUID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Story_StoryGUID] DEFAULT (newid()),
> [Name] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
> [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [DestinationID] [int] NOT NULL ,
> CONSTRAINT [PK_Story] PRIMARY KEY CLUSTERED
> (
> [StoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Photo] (
> [PhotoID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SlidePath] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
> [ThumbnailPath] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_
AS NOT
> NULL ,
> [Caption] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [StoryID] [int] NOT NULL ,
> CONSTRAINT [PK_Photo] PRIMARY KEY CLUSTERED
> (
> [PhotoID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Photo_Story] FOREIGN KEY
> (
> [StoryID]
> ) REFERENCES [Story] (
> [StoryID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> My goal is to have a single row for each Story that matches DestinationID,
> the first two columns are from story, the third is the SlidePath column fr
om
> photo. Thanks.|||Here you go (You will need to turn Identity insert off):
--Insert Stories
INSERT INTO Story(StoryID, StoryGUID, [Name], [Description], Destina
tionID)
VALUES(1,'BB12635D-3105-41CC-8345-B44086896DFC','Story 1', 'Lorem ipsum', 2)
INSERT INTO Story(StoryID, StoryGUID, [Name], [Description], Destina
tionID)
VALUES(2,'02ADA926-929F-4DAD-ABA4-29745787ABF3','Story 2', 'Lorem ipsum', 1)
INSERT INTO Story(StoryID, StoryGUID, [Name], [Description], Destina
tionID)
VALUES(3,'B8C2F642-69FC-4637-81CA-E3969BD4BBFA','Story 3', 'Lorem ipsum', 1)
--Insert Photos
INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
VALUES(1, '/photos/img1_slide.jpg', '/photos/img1_thumb.jpg','cap 1',3)
INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
VALUES(2, '/photos/img2_slide.jpg', '/photos/img2_thumb.jpg','cap 2',3)
INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
VALUES(3, '/photos/img3_slide.jpg', '/photos/img3_thumb.jpg','cap 3',2)
INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
VALUES(4, '/photos/img4_slide.jpg', '/photos/img4_thumb.jpg','cap 4',1)
If we set DestinationID=1, the result should be:
02ADA926-929F-4DAD-ABA4-29745787ABF3 Story 2 /photos/img3_slide.jpg
B8C2F642-69FC-4637-81CA-E3969BD4BBFA Story 3 /photos/img1_slide.jpg
Thanks.
"Aaron [SQL Server MVP]" wrote:

> sample data, and desired results?
> On 2/28/05 7:09 AM, in article
> DA0302E5-86CD-4025-B414-903870B776B5@.microsoft.com, "jmhmaine"
> <jmh@.online.nospam> wrote:
>
>|||Here's one way. Hard to tell if this makes sense for all potential
destinationIDs given the minimal sample data...
SELECT s.StoryGUID, s.Name, p.slidePath
FROM Story s
INNER JOIN
(
SELECT StoryID, SlidePath = MIN(SlidePath)
FROM Photo
GROUP BY StoryID
) p
ON s.StoryID = p.StoryID
WHERE DestinationID = @.destinationID
http://www.aspfaq.com/
(Reverse address to reply.)
"jmhmaine" <jmh@.online.nospam> wrote in message
news:1B1DCE18-5D47-4FE0-95AC-C1CCBEAC1E3D@.microsoft.com...
> Here you go (You will need to turn Identity insert off):
> --Insert Stories
> INSERT INTO Story(StoryID, StoryGUID, [Name], [Description],
DestinationID)
> VALUES(1,'BB12635D-3105-41CC-8345-B44086896DFC','Story 1', 'Lorem ipsum',
2)
> INSERT INTO Story(StoryID, StoryGUID, [Name], [Description],
DestinationID)
> VALUES(2,'02ADA926-929F-4DAD-ABA4-29745787ABF3','Story 2', 'Lorem ipsum',
1)
> INSERT INTO Story(StoryID, StoryGUID, [Name], [Description],
DestinationID)
> VALUES(3,'B8C2F642-69FC-4637-81CA-E3969BD4BBFA','Story 3', 'Lorem ipsum',
1)[vbcol=seagreen]
>
> --Insert Photos
> INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
> VALUES(1, '/photos/img1_slide.jpg', '/photos/img1_thumb.jpg','cap 1',3)
> INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
> VALUES(2, '/photos/img2_slide.jpg', '/photos/img2_thumb.jpg','cap 2',3)
> INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
> VALUES(3, '/photos/img3_slide.jpg', '/photos/img3_thumb.jpg','cap 3',2)
> INSERT INTO Photo(PhotoID, SlidePath, ThumbnailPath, Caption, StoryID)
> VALUES(4, '/photos/img4_slide.jpg', '/photos/img4_thumb.jpg','cap 4',1)
>
> If we set DestinationID=1, the result should be:
> 02ADA926-929F-4DAD-ABA4-29745787ABF3 Story 2 /photos/img3_slide.jpg
> B8C2F642-69FC-4637-81CA-E3969BD4BBFA Story 3 /photos/img1_slide.jpg
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
,[vbcol=seagreen]
NULL ,[vbcol=seagreen]
NOT[vbcol=seagreen]
DestinationID,[vbcol=seagreen]
column from[vbcol=seagreen]|||Great job Aaron! I can see why Microsoft made you an MVP!
I had a feeling a Group By should be in the SQL, but didn't know to apply
it. One requirement I forgot to mention was that in some cases, there isn't
a photo, so we still need to return the first two columns, but the slidePath
column would be empty. Classic Outer Join, but where would we insert this in
the code? Thanks.
Josh.
"Aaron [SQL Server MVP]" wrote:

> Here's one way. Hard to tell if this makes sense for all potential
> destinationIDs given the minimal sample data...
>
> SELECT s.StoryGUID, s.Name, p.slidePath
> FROM Story s
> INNER JOIN
> (
> SELECT StoryID, SlidePath = MIN(SlidePath)
> FROM Photo
> GROUP BY StoryID
> ) p
> ON s.StoryID = p.StoryID
> WHERE DestinationID = @.destinationID
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "jmhmaine" <jmh@.online.nospam> wrote in message
> news:1B1DCE18-5D47-4FE0-95AC-C1CCBEAC1E3D@.microsoft.com...
> DestinationID)
> 2)
> DestinationID)
> 1)
> DestinationID)
> 1)
> ,
> NULL ,
> NOT
> DestinationID,
> column from
>
>|||Did you try just changing INNER JOIN to LEFT OUTER JOIN?
http://www.aspfaq.com/
(Reverse address to reply.)
"jmhmaine" <jmh@.online.nospam> wrote in message
news:5AFE4714-192E-4194-A3EB-18B55DFC26BE@.microsoft.com...
> Great job Aaron! I can see why Microsoft made you an MVP!
> I had a feeling a Group By should be in the SQL, but didn't know to apply
> it. One requirement I forgot to mention was that in some cases, there
isn't
> a photo, so we still need to return the first two columns, but the
slidePath
> column would be empty. Classic Outer Join, but where would we insert this
in[vbcol=seagreen]
> the code? Thanks.
> Josh.
> "Aaron [SQL Server MVP]" wrote:
>
ipsum',[vbcol=seagreen]
ipsum',[vbcol=seagreen]
ipsum',[vbcol=seagreen]
1',3)[vbcol=seagreen]
2',3)[vbcol=seagreen]
3',2)[vbcol=seagreen]
4',1)[vbcol=seagreen]
NULL[vbcol=seagreen]
,[vbcol=seagreen]
NOT[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS[vbcol=seagr
een]
NULL ,[vbcol=seagreen]|||That worked, I tried just OUTER JOIN which didn't work. Thanks again for you
r
help!
Josh.
"Aaron [SQL Server MVP]" wrote:

> Did you try just changing INNER JOIN to LEFT OUTER JOIN?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "jmhmaine" <jmh@.online.nospam> wrote in message
> news:5AFE4714-192E-4194-A3EB-18B55DFC26BE@.microsoft.com...
> isn't
> slidePath
> in
> ipsum',
> ipsum',
> ipsum',
> 1',3)
> 2',3)
> 3',2)
> 4',1)
> NULL
> ,
> NOT
> SQL_Latin1_General_CP1_CI_AS
> NULL ,
>
>