Design Idea

2 Jun 2005 5:59 PM

XXX

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?

2 Jun 2005 6:09 PM

Rick Sawtell

Show quote Hide quote

"XXX" <s*@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

2 Jun 2005 9:37 PM

Hugo Kornelis

On Thu, 2 Jun 2005 13:59:32 -0400, XXX wrote:

Show quoteHide quote

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

3 Jun 2005 2:14 PM

XXX

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

Show quoteHide quote

"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:
>
>>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)