CODEWITHSUNDEEP
sqloracle-database
Pawan Gurung
Pawan Gurung

Reputation: 1

How to get distinct username after join query, ORACLE

2 tables user table and role_post table( state column is defined in role_post table), One user can have many role_post rows even with in one state. SO i want 10 distinct users of same state, can i modify my query?

  SELECT
    u.username,
    u.full_name,
    u.created_at,
    r.role_id r_id,
    rp.state,
FROM
    user_account u
INNER JOIN ROLE r ON
    u.user_id = r.user_id
LEFT JOIN role_post rp ON
    r.role_id = rp.role_id
LEFT JOIN post p ON
    p.post_id = rp.post_id
INNER JOIN (
    SELECT
        *
    FROM
        (
        SELECT
            u.user_id
        FROM
            user_account u
        LEFT JOIN ROLE_POST rp2 ON
            u.ROLE_ID = rp2.ROLE_ID
        WHERE
            rp2.STATE = '001'
        ORDER BY
            u.created_at DESC
    WHERE
        ROWNUM <= 10 ) uu ON
    uu.user_id = u.user_id
ORDER BY
    u.created_at DESC

Upvotes: 0

Views: 83

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

Your query raises a few questions about your data model and that makes it hard to give a confident answer.

  • Why are you doing a LEFT JOIN on some of your tables when you specifically want your results to include rows where matches in those table exist?
  • Why are you joining in POST when it is not used anywhere?
  • Why does USER_ACCOUNT have a ROLE_ID column, if one user can take on multiple roles?

So, ignoring all that, I'd suggest you look in to the CROSS APPLY feature in conjuction with FETCH FIRST x ROWS ONLY. You can get a list of users from USER_ACCOUNT, then CROSS APPLY those with the first record for ROLE_POST that has the state you are looking for. Because you're only joining to 1 row for each user, you'll still just have one row per user at the end of that. Then, limit your results to whichever 10 users you want from that list (e.g., the 10 most recently created users).

Put all together, it might look something like this:

SELECT u.username,
       u.full_name,
       u.created_at,
       x.role_id r_id,
       x.state
FROM   user_account u
CROSS APPLY ( SELECT  r.role_id, rp.state
              FROM    role r 
              INNER JOIN role_post rp ON rp.role_id = r.role_id
              WHERE   r.user_id = u.user_id 
              AND     rp.state = '001'
              -- Best to ORDER BY something here so results are predictable and consistent
              ORDER BY ???
              FETCH FIRST 1 ROW ONLY ) x
ORDER BY u.created_at DESC
FETCH FIRST 10 ROWS ONLY;

Upvotes: 1

Related Questions