Null values: parent

Showing posts with label parent. Show all posts

Monday, March 12, 2012

Reverse Bill Of Materials Query?

Hello!
We have a bill of materials table with a classic parent/child relationships.
What I need to be able to do is to take a specific part number and return
the highest level parent for that part number.
For instance, If part number A is used as a component in part number B, and
part number B is then used in another component called C, then the highest
level parent for part number A is C.
so the result of the query when ran against part number A would be C
How can I set up such a query?
Thanks
JoeBelow is a solution using a user-defined function. If this
is a frequent requirement, you may want to consider alternate
ways of modeling your hierarchy. If you search groups.google.co.uk
or www.google.com for hierarchy+itzik+sqlserver you'll find some nice
ideas.
-- Original thread at http://groups.google.co.uk/groups?q=A9B05D_C5E784
CREATE TABLE Employee (
pk int not null primary key,
parent int
)
go
INSERT INTO Employee VALUES (1,NULL)
INSERT INTO Employee VALUES (2,NULL)
INSERT INTO Employee VALUES (3,1)
INSERT INTO Employee VALUES (4,2)
INSERT INTO Employee VALUES (5,4)
go
CREATE FUNCTION rootPK(
@.pk INT
) RETURNS INT
AS
BEGIN
DECLARE @.parent INT, @.this INT
SET @.this = NULL
SET @.parent = @.pk
WHILE @.parent IS NOT NULL BEGIN
SELECT
@.this
= pk
, @.parent
= parent
FROM
Employee
WHERE
pk
= @.parent
END
RETURN @.this
END
go
ALTER TABLE Employee ADD rootPK as dbo.rootPK(pk)
go
SELECT * FROM Employee
go
-- drop table Employee
-- drop function dbo.rootPK
-- Steve Kass
-- Drew University
Joe Williams wrote:

> Hello!
> We have a bill of materials table with a classic parent/child relationship
s.
> What I need to be able to do is to take a specific part number and return
> the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B, an
d
> part number B is then used in another component called C, then the highest
> level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>|||There is also a good example at:
http://msdn.microsoft.com/library/d...r />
_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <Joe@.anywhere.com> schrieb im Newsbeitrag
news:%234YOY0jWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have a bill of materials table with a classic parent/child
> relationships. What I need to be able to do is to take a specific part
> number and return the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B,
> and part number B is then used in another component called C, then the
> highest level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>

Reusing package configuration in child packages

I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.

Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?

Sachin

I have found that one file per connection is ultimately the best way to go. You may be able to have more than that, if you are sure you will always use the same configurations at the same time, but that seems to be more useful when you have some variables, perhaps a couple of file paths used for all of your packages to do logging or check pointing, but you need to be careful of not falling into the same trap again of over grouping. Keep it at the lowest level you can, it is just more flexible I have found, and copes better with change as well. (Deleted Post?)

|||

Darren

Looks like that might very well be the way to go then. I wonder if there is any way to include other config files into a single file, but that's for another day.

Thanks for the prompt reply.

Sachin

|||

Nice idea, support for XML Inclusions (XInclude) would do it (http://www.w3.org/TR/xinclude/).

One for MS Connect I think - http://connect.microsoft.com

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.
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Monday, March 12, 2012

Reverse Bill Of Materials Query?

Reusing package configuration in child packages

Tuesday, February 21, 2012

Returning Just one row per parent

Returning Just one row per parent

Returning Just one row per parent

Null values

Blog Archive

About Me