Sql query to get rows which have a column with multiple values at the same time-CodePudding

I have three tables: POST, POST_ROLE and ROLE which portion of them is as below. POST and ROLE have a many to many relation and their join table is POST_ROLE.

How to get posts which have different roles at the same time?

For example, how to get posts which Have roles ROLE_USER and ROLE_ADMIN at the same time?

A query as below, doesn't return anything, because a row can't have two values for one column:

SELECT
    *
FROM
    POST op
JOIN POST_ROLE opr ON
    op.POST_ID = opr.POST_ID
JOIN ROLE or1 ON
    opr.ROLE_ID = or1.ROLE_ID
WHERE
    or1.CODE = 'ROLE_USER' AND or1.CODE = 'ROLE_ADMIN';

Another query which use IN operator, retun all three post rows, because IN operation works as OR operator here.

SELECT
    *
FROM
    POST op
JOIN POST_ROLE opr ON
    op.POST_ID = opr.POST_ID
JOIN ROLE or1 ON
    opr.ROLE_ID = or1.ROLE_ID
WHERE
    or1.CODE IN ('ROLE_USER','ROLE_ADMIN');

The preferred result of query should be something as below:

POST_ID	TITLE	ROLE_ID	CODE
1	CEO	100	ROLE_USER
1	CEO	101	ROLE_ADMIN

POST table:

POST_ID	TITLE
1	CEO
2	CTO

ROLE table:

ROLE_ID	CODE
100	ROLE_USER
101	ROLE_ADMIN
102	ROLE_ANONYMOUS

POST_ROLE table:

POST_ID	ROLE_ID
1	100
1	101
2	101

CodePudding user response：

The solution is:

SELECT
    *
FROM
    POST p
WHERE
    EXISTS (
    SELECT
        1
    FROM
        POST_ROLE pr
    JOIN ROLE r ON
        r.ROLE_ID = pr.ROLE_ID
    WHERE
        p.POST_ID = pr.POST_ID
        AND r.CODE = 'ROLE_USER')
    AND EXISTS (
    SELECT
        1
    FROM
        POST_ROLE pr
    JOIN ROLE r ON
        r.ROLE_ID = pr.ROLE_ID
    WHERE
        p.POST_ID = pr.POST_ID
        AND r.CODE = 'ROLE_ADMIN');