Null values: relationship

Showing posts with label relationship. Show all posts

Saturday, February 25, 2012

Returning only one row from joined table

Hello
Given two tables in a one-to-many relationship, how do I write a query to
return only one row from the table on the "many" side of the relationship?
e.g., in the "pubs" sample database, the following query returns all rows
from the Authors table and all related rows from the TitleAuthor table:
SELECT
A.au_id,
A.au_lname,
A.au_fname,
TA.title_id,
TA.royaltyper
FROM
Authors A
LEFT OUTER JOIN TitleAuthor TA ON TA.au_id = A.au_id
ORDER BY
A.au_id,
TA.title_id
In my case, I want all rows from the Authors table and for related rows in
the TitleAuthor table, only the first row (ordered by title_id).
Thanks!!!
AlexSELECT
A.au_id,
A.au_lname,
A.au_fname,
TA.title_id,
TA.royaltyper
FROM Authors A
LEFT JOIN
(SELECT au_id, MIN(title_id) AS title_id
FROM TitleAuthor
GROUP BY au_id) AS T
ON T.au_id = A.au_id
LEFT JOIN
TitleAuthor TA
ON TA.au_id = T.au_id
AND TA.title_id = T.title_id
ORDER BY
A.au_id,
TA.title_id ;
David Portas
SQL Server MVP
--|||Select TOP1 attribute_list
From table
etc...
Alex wrote:
> Hello
> Given two tables in a one-to-many relationship, how do I write a query to
> return only one row from the table on the "many" side of the relationship?
> e.g., in the "pubs" sample database, the following query returns all rows
> from the Authors table and all related rows from the TitleAuthor table:
> SELECT
> A.au_id,
> A.au_lname,
> A.au_fname,
> TA.title_id,
> TA.royaltyper
> FROM
> Authors A
> LEFT OUTER JOIN TitleAuthor TA ON TA.au_id = A.au_id
> ORDER BY
> A.au_id,
> TA.title_id
> In my case, I want all rows from the Authors table and for related rows in
> the TitleAuthor table, only the first row (ordered by title_id).
> Thanks!!!
> Alex
>

Tuesday, February 21, 2012

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 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 there
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 there
> is a third table called Destination that has a one to many relationship with
> 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 NULL ,
[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? 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 there
> > is a third table called Destination that has a one to many relationship with
> > Story. One destination has many stories.
> >
> > Thanks.
>|||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 NULL ,
> [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.|||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)
--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:
> > 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 NULL ,
> > [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.
>|||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)
>
> --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:
> >
> > > 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 NULL
,
> > > [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.
> >
> >|||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...
> > 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)
> >
> >
> > --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:
> > >
> > > > 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 NULL
> ,
> > > > [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.
> > >
> > >
>
>|||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
> 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...
> > > 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)
> > >
> > >
> > > --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:
> > > >
> > > > > 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
NULL
> > ,
> > > > > [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.
> > > >
> > > >
> >
> >
> >|||That worked, I tried just OUTER JOIN which didn't work. Thanks again for your
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...
> > 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...
> > > > 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)
> > > >
> > > >
> > > > --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:
> > > > >
> > > > > > 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
> NULL
> > > ,
> > > > > > [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.
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Returning Just one row per parent

Returning errors from a stored procedure using transactions

I'm having a brain cramp.
I'm writing a stored procedure that will do an insert into two tables in a
parent-child relationship. I want either both inserts to succeed or both to
fail. Obviously, a transaction is required. If an error occurrs any time
during the SP execution, I want to return the error to the application in th
e
same manner as SQL Server would if I wasn't using a transaction. So, my SP
looks something like:
DECLARE @.TheError INT
BEGIN TRAN
INSERT Parent...
SET @.TheError = @.@.ERROR
IF @.TheError = 0
BEGIN
INSERT Child...
SET @.TheError = @.@.ERROR
END
IF @.TheError = 0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
If I Rollback the transaction, how do I return the error to the application
such that the CLR will generate the appropriate SQL Exception in my
application?
BOL wasn't clear about this.
Thanks,
BobThe original error will be returned to the application no matter what you
do. You can't _trap_ errors in SQL Server 2000, only handle them.
If you want to have really short code and get the behaviour you want, you
can use:
SET XACT_ABORT ON
BEGIN TRAN
INSERT Parent...
INSERT Child...
COMMIT TRAN
If there is an error, the transaction will automatically be aborted and
rolled back, and the error is still raised to the application. Note that no
further code will be executed after the statement where the error happens.
Jacco Schalkwijk
SQL Server MVP
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:920AF55A-7551-4B05-AE55-C77D49D35391@.microsoft.com...
> I'm having a brain cramp.
> I'm writing a stored procedure that will do an insert into two tables in a
> parent-child relationship. I want either both inserts to succeed or both
> to
> fail. Obviously, a transaction is required. If an error occurrs any time
> during the SP execution, I want to return the error to the application in
> the
> same manner as SQL Server would if I wasn't using a transaction. So, my SP
> looks something like:
> DECLARE @.TheError INT
> BEGIN TRAN
> INSERT Parent...
> SET @.TheError = @.@.ERROR
> IF @.TheError = 0
> BEGIN
> INSERT Child...
> SET @.TheError = @.@.ERROR
> END
> IF @.TheError = 0
> BEGIN
> COMMIT TRAN
> END
> ELSE
> BEGIN
> ROLLBACK TRAN
> END
> If I Rollback the transaction, how do I return the error to the
> application
> such that the CLR will generate the appropriate SQL Exception in my
> application?
> BOL wasn't clear about this.
> Thanks,
> Bob
>|||Jacco,
Thank you. I vaguely remembered something like that, but since BOL didn't
explicitly say that, I wasn't sure. I'm glad I asked because I had completel
y
forgotten about XACT_ABORT.
To maximize reuseability, I usually write my SPs to do insert/update
operations on a single table and let my application code handle the
transactions, but in this case, a parent without a child row is not valid
from a business perspective, so I decided to let the SP do both inserts.
Bob
"Jacco Schalkwijk" wrote:

> The original error will be returned to the application no matter what you
> do. You can't _trap_ errors in SQL Server 2000, only handle them.
> If you want to have really short code and get the behaviour you want, you
> can use:
> SET XACT_ABORT ON
> BEGIN TRAN
> INSERT Parent...
> INSERT Child...
> COMMIT TRAN
> If there is an error, the transaction will automatically be aborted and
> rolled back, and the error is still raised to the application. Note that n
o
> further code will be executed after the statement where the error happens.
>
> --
> Jacco Schalkwijk
> SQL Server MVP

Saturday, February 25, 2012

Returning only one row from joined table

Tuesday, February 21, 2012

Returning Just one row per parent

Returning Just one row per parent

Returning Just one row per parent

Returning errors from a stored procedure using transactions

Null values

Blog Archive

About Me