Отображение нескольких категорий для одной записи

Question

У меня есть следующие таблицы:

1. Таблица категорий, которая имеет столбец идентификатора, столбец описания и столбцы родительского идентификатора категории следующим образом:

cat_id | cat_description | cat_pid
------- + ----------------- + --------
1 | Государство | 0
2      | Техас | 1
3 | Калифорния | 1
4      | Название | 0
5      | Инженер | 4
6      | Юрист | 4
7      | Фермер | 4
8      | Кредитная карта | 0
9      | Виза | 8
10     | Мастер Кард | 8...

2. Таблица клиентов с идентификатором и именем клиента:

cust_id | CUST_NAME 

--------+-----------
111111  | Джон
222222 | Дэвид
333333 | Крис
444444  | отметка...

3. Category_Has_Customer, который является многим для многих отношений между клиентом и категорией.

chc_cust_id | chc_cat_id
------------+-----------
111111  | 2
111111 | 5
111111 | 9
222222 | 7
222222 | 3
333333 | 6

Категория имеет только два уровня глубины.

В моем приложении клиент может иметь ноль или более категорий. Я хотел бы отобразить некоторые или все категории, которые есть у клиента. Например, если я хочу отобразить все категории, я хотел бы иметь следующую таблицу:

cust_name | Государство | Название | Кредитная карта
---------- + ------------ + ---------- + ------------
Джон | Техас | Инженер | виза
Дэвид | Калифорния | Фермер |
Крис | | Юрист |
Марк | | |

Я также должен иметь возможность отображать определенные категории, например, только название и кредитная карта:

cust_name | Название | Кредитная карта
---------- + ---------- + ------------
Джон | Инженер | виза
Дэвид | Фермер |
Крис | Юрист |
Марк | | 

Я пытался сделать это с левым соединением, что-то вроде:

SELECT c1.cust_id, c1.cust_name, t1.cat_desc as State
FROM Customer c1, Category_has_Customer chc
LEFT JOIN Category t1 ON t1.cat_pid = '1' AND chc.chc_cat_id = t1.cat_id
WHERE c1.cust_id = chc.chc_cust_id

Но это не помогло, так как я получил дублированные строки.

Заранее спасибо за помощь

Answer 1

Я попробую, должен работать на всех реализациях SQL, но я сделал в t-SQL:

С этими данными

declare @Category table (cat_id int, cat_description varchar(20), cat_pid int)
declare @Customer table (cust_id int, cust_name varchar(20))
declare @Customer_Has_Category table (chc_cust_id int, chc_cat_id int)

insert into @Category (cat_id, cat_description, cat_pid) values (1,'State',0)
insert into @Category (cat_id, cat_description, cat_pid) values (2,'Texas',1)
insert into @Category (cat_id, cat_description, cat_pid) values (3,'California',1)
insert into @Category (cat_id, cat_description, cat_pid) values (4,'Title',0)
insert into @Category (cat_id, cat_description, cat_pid) values (5,'Engineer',4)
insert into @Category (cat_id, cat_description, cat_pid) values (6,'Lawyer',4)
insert into @Category (cat_id, cat_description, cat_pid) values (7,'Farmer',4)
insert into @Category (cat_id, cat_description, cat_pid) values (8,'Credit Card',0)
insert into @Category (cat_id, cat_description, cat_pid) values (9,'Visa',8)
insert into @Category (cat_id, cat_description, cat_pid) values (10,'Master Card',8)

insert into @Customer (cust_id, cust_name) values (111111, 'John')
insert into @Customer (cust_id, cust_name) values (222222, 'David')
insert into @Customer (cust_id, cust_name) values (333333, 'Chris')
insert into @Customer (cust_id, cust_name) values (444444, 'Mark')

insert into @Customer_Has_Category (chc_cust_id, chc_cat_id) values (111111, 2)
insert into @Customer_Has_Category (chc_cust_id, chc_cat_id) values (111111, 5)
insert into @Customer_Has_Category (chc_cust_id, chc_cat_id) values (111111, 9)
insert into @Customer_Has_Category (chc_cust_id, chc_cat_id) values (222222, 7)
insert into @Customer_Has_Category (chc_cust_id, chc_cat_id) values (222222, 3)
insert into @Customer_Has_Category (chc_cust_id, chc_cat_id) values (333333, 6)

Этот запрос

select cust_name, MAX(State) as State, MAX(Title) as Title, MAX(CreditCard) as CreditCard
from
(
select 
c.cust_name,
(case when cat.cat_pid = 1 then cat_description else '' end) as State,
(case when cat.cat_pid = 4 then cat_description else '' end) as Title,
(case when cat.cat_pid = 8 then cat_description else '' end) as CreditCard
from @Customer c 
left outer join @Customer_Has_Category chc on c.cust_id = chc.chc_cust_id
left outer join @Category cat on chc.chc_cat_id = cat.cat_id
) as subq
group by cust_name

дает

cust_name            State                Title                CreditCard
-------------------- -------------------- -------------------- --------------------
Chris                                     Lawyer               
David                California           Farmer               
John                 Texas                Engineer             Visa
Mark   

Если вы хотите удалить State, просто удалите его из оператора select.