Design Idea

Need some design ideas.
I have to create a table which will have 2 types of IDs
MainID SubID
1 1
1 2
2 1
2 2
3 100
3 101
Basically I want to auto generate SubID for different MainIDs. For 2
different MainIDs, SubIDs can be same. Once the starting number is
determined the increment is always by 1.
Any ideas how this can be achived?"XXX" <sa@.nomail.com> wrote in message
news:#BH3Wz5ZFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Need some design ideas.
> I have to create a table which will have 2 types of IDs
>
> MainID SubID
> 1 1
> 1 2
> 2 1
> 2 2
> 3 100
> 3 101
>
> Basically I want to auto generate SubID for different MainIDs. For 2
> different MainIDs, SubIDs can be same. Once the starting number is
> determined the increment is always by 1.
> Any ideas how this can be achived?
>
A trigger would probable work in this situation.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 2 Jun 2005 13:59:32 -0400, XXX wrote:

>Need some design ideas.
>I have to create a table which will have 2 types of IDs
>
>MainID SubID
>1 1
>1 2
>2 1
>2 2
>3 100
> 3 101
>
>Basically I want to auto generate SubID for different MainIDs. For 2
>different MainIDs, SubIDs can be same. Once the starting number is
>determined the increment is always by 1.
>Any ideas how this can be achived?
>
INSERT INTO MyTable (MainID, SubID, OtherColumns)
SELECT @.MainID, -- Passed in as parameter
COALESCE((SELECT MAX(SubID)
FROM MyTable
WHERE MainID = @.MainID),
@.StartofSubID), -- Passed in as parameter
@.OtherColumns -- Passed in as parameters
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
It works. I think you missed the '+1' in your query.
INSERT INTO MyTable (MainID, SubID, OtherColumns)
SELECT @.MainID, -- Passed in as parameter
COALESCE((SELECT MAX(SubID)
FROM MyTable
WHERE MainID = @.MainID) +1 ,
@.StartofSubID), -- Passed in as parameter
@.OtherColumns -- Passed in as parameters
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:1suu91djb3l14fu50enarjf5u5dliagpan@.
4ax.com...
> On Thu, 2 Jun 2005 13:59:32 -0400, XXX wrote:
>
> INSERT INTO MyTable (MainID, SubID, OtherColumns)
> SELECT @.MainID, -- Passed in as parameter
> COALESCE((SELECT MAX(SubID)
> FROM MyTable
> WHERE MainID = @.MainID),
> @.StartofSubID), -- Passed in as parameter
> @.OtherColumns -- Passed in as parameters
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)