Store your group_types
as a hieararchical table (with nested sets
or parent-child
model):
Parent-child
:
typeid parent name
1 0 Buyers
2 0 Sellers
3 0 Referee
4 1 Electrical
5 1 Mechanic
SELECT *
FROM mytable
WHERE group IN
(
SELECT typeid
FROM group_types
START WITH
typeid = 1
CONNECT BY
parent = PRIOR typeid
)
will select all buyers in Oracle
.
Nested sets
:
typeid lower upper Name
1 1 2 Buyers
2 3 3 Sellers
3 4 4 Referee
4 1 1 Electrical
5 2 2 Mechanic
SELECT *
FROM group_types
JOIN mytable
ON group BETWEEN lower AND upper
WHERE typeid = 1
will select all buyers in any database.
Nested sets
is implementable anywhere and more performant, if you don't need hierarchical ordering or frequent updates on group_types
.
Parent-child
is implementable easily in Oracle
and SQL Server
and with a little effort in MySQL
. It allow easy structure changing and hierarchical ordering.
See this article in my blog on how to implement it in MySQL
: