CODEWITHSUNDEEP
sqlsql-serveroracle-databasesortingmany-to-many
fashuser
fashuser

Reputation: 1479

SQL sort records which have many to many relations in alphabetical order

I have following db tables: COMMENT and TAG and COMMENT_TAG. There is many to many relation between them, actually single comment can have multiple tags and single tags can be used in multiple comments. Let's imagine following:

COMMENT
---------------
ID      TEXT
1       comment1
2       comment2
3       comment3
4       comment4


TAG
---------------
ID      NAME
1       bb
2       cc
3       ca
4       aa
5       ac
6       bd


COMMENT_TAG
---------------
ID_COMMENT      ID_TAG
1               1
1               2
1               3
1               4
2               1
2               3
3               3
3               4
3               5
3               6
4               1
4               2
4               3
4               4
4               5
4               6

As result of this on FE side following data is shown

Comment     Tags
==================================
comment1    bb, cc, ca, aa
comment2    bb, ca
comment3    ca, aa, ac, bd
comment4    bb, cc ,ca, aa, ac, bd

I would like to make a sort in following way:

1 Sort tags per each comment in alphabetical order:

Comment     Tags
==================================
comment1    aa, bb, ca, cc
comment2    bb, ca
comment3    aa, ac, bd, ca
comment4    aa, ac, bb, bd, ca, cc

2 Sort comments by tags in alphabetical order:

Comment     Tags
==================================
comment4    aa, ac, bb, bd, ca, cc
comment3    aa, ac, bd, ca
comment1    aa, bb, ca, cc
comment2    bb, ca

Could you please advice the way how it can be achieved with SQL or advice some alternative ways of sorting for such use case?

Upvotes: 1

Views: 74

Answers (1)

forpas
forpas

Reputation: 164139

Join the tables and group by comment.
Then use a function like LISTAGG() for Oracle 11g+, to concatenate all the tags for each comment:

select c.text, listagg(t.name, ',') within group (order by t.name) tags
from "COMMENT" c
left join COMMENT_TAG ct on ct.id_comment = c.id
left join TAG t on t.id = ct.id_tag
group by c.id, c.text

Add the ORDER BY clause for each case:

order by c.text

or:

order by tags

See the demo.

Upvotes: 3

Related Questions