Check if a value exists in the child-parent tree

Question

I'm creating a simple directory listing page where you can specify what kind of thing you want to list in the directory e.g. a person or a company.

Each user has an UserTypeID and there is a dbo.UserType lookup table. The dbo.UserType lookup table is like this:

UserTypeID | UserTypeParentID | Name
1            NULL               Person
2            NULL               Company
3            2                  IT
4            3                  Accounting Software

In the dbo.Users table we have records like this:

UserID  | UserTypeID |  Name
1           1           Jenny Smith
2           1           Malcolm Brown
3           2           Wall Mart
4           3           Microsoft
5           4           Sage

My SQL (so far) is very simple: (excuse the pseudo-code style)

DECLARE @UserTypeID int
   SELECT 
        *
    FROM
        dbo.Users u
    INNER JOIN
        dbo.UserType ut
    WHERE
        ut.UserTypeID = @UserTypeID

The problem is here is that when people want to search for companies they will enter in '2' as the UserTypeID. But both Microsoft and Sage won't show up because their UserTypeIDs are 3 and 4 respectively. But its the final UserTypeParentID which tells me that they're both Companies.

How could I rewrite the SQL to ask it to return to return records where the UserTypeID = @UserTypeID or where its final UserTypeParentID is also equal to @UserTypeID. Or am I going about this the wrong way?

You can make this schema and your queries much more simpler by removing this `UserTypeParentID` from `UserType` table. Make `UserID` and `UserTypeID` a Composite Primary key. As they say `KEEP IT SIMPLE` :) — M.Ali, Sep 07 '14 at 22:11
@M.Ali I don't think I need to change the schema. I think it would be some kind of CTE expression in the WHERE clause? — volume one, Sep 07 '14 at 22:20
Yes you will need a recursive CTE, but writing recursive CTEs is no fun, and you certainly dont want to put yourself in a situation when you are writing recursive CTEs only to get a type of a company which is a very basic query but writing it with a recursive query will be painful. — M.Ali, Sep 07 '14 at 22:26

score 2 · Answer 1 · answered Sep 07 '14 at 22:23

2

Schema Change

I would suggest you to break it down this schema a little bit more, to make your queries and life simpler, with this current schema you will end up writing a recursive query every time you want to get simplest data from your Users table, and trust me you dont want to do this to yourself.

I would break down this schema of these tables as follow:

dbo.Users

UserID   |   UserName 
  1      |   Jenny
  2      |   Microsoft
  3      |   Sage

dbo.UserTypes_Type

TypeID  |  TypeName
  1     |   Person
  2     |     IT
  3     |   Compnay
  4     |  Accounting Software

dbo.UserTypes

UserID   |  TypeID
   1     |     1
   2     |     2
   2     |     3
   3     |     2
   3     |     3
   3     |     4

answered Sep 07 '14 at 22:23

M.Ali

67,945
13
101
127

I agree this makes this easier. – volume one Sep 07 '14 at 23:58
This has made it easier for a select. however, can you suggest what I should do to get all the parent values of a UserTypeID when inserting a new user? The user will say he is UserTypeID of 4. I then need to insert into the dbo.User_UserType table (the many-to-many table) the UserID and ALL their applicable UserTypeIDs. So something like `Insert into dbo.User_UserType (UserID, UserTypeID) (Select u.UserID, ut.UserTypeID FROM dbo.Users u JOIN dbo.UserTypes ut on ut.UserTypeID = @UserTypeID ....)` I'm not sure how to do this part? – volume one Sep 08 '14 at 09:11

score 1 · Answer 2 · edited May 23 '17 at 11:49

You say that you are "creating" this - excellent because you have the opportunity to reconsider your whole approach.

Dealing with hierarchical data in a relational database is problematic because it is not designed for it - the model you choose to represent it will have a huge impact on the performance and ease of construction of your queries.

You have opted for an Adjacently List model which is great for inserts (and deletes) but a bugger for selects because the query has to effectively reconstruct the hierarchy path. By the way an Adjacency List is the model almost everyone goes for on their first attempt.

Everything is a trade off so you should decide what queries will be most common - selects (and updates) or inserts (and deletes). See this question for starters. Also, since SQL Server 2008, there is a native HeirachyID datatype (see this) which may be of assistance.

Of course, you could store your data in an XML file (in SQL Server or not) which is designed for hierarchical data.

Check if a value exists in the child-parent tree

2 Answers2

Schema Change