Results 1 to 5 of 5
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    MSSQL Self-Join Default Value to Itself

    Hi all,

    I'm trying to setup a couple of hiearchies in my new SQL Server database. For example Product Categories.

    This is the table definition:

    Click image for larger version. 

Name:	category1.PNG 
Views:	26 
Size:	53.2 KB 
ID:	47960

    The data should look like this:


    ProductCategoryID ParentID ProductCategory
    1 1 Main category 1
    2 2 Main category 2
    3 1 Sub1 of Main 1
    4 1 Sub2 of Main 1
    5 4 SubSub1 of Sub2
    6 2 Sub2 of Main 2
    7 7 Main category 3
    8 7 Sub1 of Main 3
    9 9 Main category 4

    I would like to assign default Parent for itself when creating a new category, so I create a main category by default.

    Something like this:
    Click image for larger version. 

Name:	category2.PNG 
Views:	25 
Size:	56.9 KB 
ID:	47961

    That clearly doesn't work because I don't know the primary key yet. And it's probably completely wrong statement within the T-SQL language.

    Can you please help me how to approach this problem? I wanna do other hiearchies as well, so I'm looking for general concepts.

    Thanks a lot in advance.

    Best regards,
    Tomas

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I would leave the default value empty and add a calculated field with property Computed column specification -> formula set to coalesce(ParentID,ProductcategoryID)

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thank you for the tip, I didn't know that function. Did you mean like this?

    Click image for larger version. 

Name:	category3.PNG 
Views:	18 
Size:	62.9 KB 
ID:	47964

    Thanks,
    Tomas

    UPDATE: Unfortunately I get this error with the definition above:
    Click image for larger version. 

Name:	category4.PNG 
Views:	15 
Size:	15.7 KB 
ID:	47965

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Try:

    CategoryParentID as (COALESCE(ParentID, ProductCategoryID)) PERSISTED

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    That worked, thanks a lot

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How is this MS Access Database Connected to MSSQL?
    By Barry123 in forum SQL Server
    Replies: 9
    Last Post: 06-08-2020, 12:27 PM
  2. Replies: 3
    Last Post: 09-20-2017, 09:50 AM
  3. Access 2013 query from mssql.
    By Rzadziu in forum Queries
    Replies: 2
    Last Post: 12-04-2013, 02:45 PM
  4. Replies: 6
    Last Post: 11-19-2013, 01:38 AM
  5. Populating identity field of an over-ODBC linked MSSQL table
    By Passiday in forum Import/Export Data
    Replies: 0
    Last Post: 11-16-2011, 11:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums