Create a view of an account showing hierarchy of tags

Question

We have a database structured as follows:

Cnsmr_accnt_tag Table

Cnsmr_accnt_id      | Tag_id
12345               | 614
12345               | 615
12345               | 741
12345               | 989
12345               | 901
12352               | 614
12352               | 675
12352               | 314
12352               | 989
12370               | 789
12370               | 412
13270               | 142
13270               | 326
13270               | 391
13270               | 469

Then we have the Tag Table

Tag_ID    | Tag_typ_ID  
614       | 800
615       | 801
741       | 802
989       | 803
675       | 801
789       | 800
412       | 801
142       | 802
314       | 802
326       | 807
391       | 802
901       | 805
469       | 804

Finally we have the Tag_typ table

Tag_typ_id     | Tag_typ_shrt_nm
800            | Area
801            | Process
802            | Other1
803            | Other2
804            | Other3
805            | Other4
806            | Other5
807            | Other6

The cnsmr_accnt can have multiple tags. The area and process tag are exclusive and an account can only have one of these. But the other types of tags are non-exclusive and an account can have many of these.

I am trying to create a query to present data as follows:

CNSMR_ACCNT_ID       |AREA_TAG  | PROCESS TAG  | OTHER TAGS
12345                |614       |   615        | 741
12345                |614       |   615        | 989
12345                |614       |   615        | 901
12352                |614       |   675        | 314
12352                |614       |   675        | 989
12370                |789       |   412        | 142
12370                |789       |   412        | 326
12370                |789       |   412        | 391
12370                |789       |   412        | 469

Any suggestions as to how to do this?

Answer 1

You can use a query with left joins like the one below:

SELECT * 
FROM   cnsmr_accnt_tag c 
   LEFT JOIN (tag ta 
              LEFT JOIN tag_type tta 
                     ON tta.tag_typ_id = ta.tag_typ_id) 
          ON c.tag_id = ta.tag_id 
             AND tta.tag_typ_shrt_nm = 'PROCESS' 
   LEFT JOIN (tag tb 
              LEFT JOIN tag_type ttb 
                     ON ttb.tag_typ_id = tb.tag_typ_id) 
          ON c.tag_id = tb.tag_id 
             AND ttb.tag_typ_shrt_nm = 'AREA' 
   LEFT JOIN (tag tc 
              LEFT JOIN tag_type ttc 
                     ON ttc.tag_typ_id = tc.tag_typ_id) 
          ON c.tag_id = tc.tag_id 
             AND ttc.tag_typ_shrt_nm NOT IN ( 'AREA', 'PROCESS' )