Question 20852 in Mysql Question posted in
The official MySQL documentation can be found here.

Sql: To fetch all record along with matching record – Mysql

Fiddle i created: http://sqlfiddle.com/#!9/e22eb6/2

Table Relation ship

Data for each tables

userdetails

authority_master

users_authority_relation

I tried below query but its only showing result which particular user has authority/permission assigned on him, i want all records(permission avaialble) in authority_master table along with matched one.

SELECT U.first_name, 
       UR.authority_id as AUTHORITY_REL_AUTH_ID,
       AM.authority 
FROM userdetails U 
INNER JOIN users_authority_relation UR 
       ON U.user_id=UR.user_id 
LEFT JOIN authority_master AM 
       ON AM.authority_id=UR.authority_id 
WHERE U.user_id=1;

 first_name   AUTHORITY_REL_AUTH_ID authority 
 admin                         1    ADMIN_USER
 admin                         2    STANDARD_USER
 admin                         4    HR_PERMISSION`

Expected output(order does not matter), how to get this output?

first_name   AUTHORITY_REL_AUTH_ID  authority
 admin                         1    ADMIN_USER
 admin                         2    STANDARD_USER 
admin                         null  NEW_CANDIDATE
 admin                         4    HR_PERMISSION
Tags:

2

Answers


  1. 0

    Can you try this :

    SELECT U.first_name, UR.authority_id as AUTHORITY_REL_AUTH_ID, AM.authority 
FROM authority_master AM
LEFT JOIN users_authority_relation UR ON AM.authority_id=UR.authority_id AND UR.user_id = 1
LEFT JOIN userdetails U ON U.user_id = 1

    Demo here

    Login or Signup to reply.
  2. 0

    It isn’t pretty, but you need the hole thing

    Query 1:

    SELECT COALESCE(U.first_name,U2.first_name)

       ,UR.authority_id as AUTHORITY_REL_AUTH_ID,
       AM.authority 
FROM ( userdetails U
INNER JOIN users_authority_relation UR 
       ON U.user_id=UR.user_id  ANd u.user_id = 1)
RIGHT JOIN (userdetails U2 CROSS JOIN authority_master AM )
       ON AM.authority_id=UR.authority_id
WHERE U2.user_id = 1

    Results:

    | COALESCE(U.first_name,U2.first_name) | AUTHORITY_REL_AUTH_ID |     authority |
|--------------------------------------|-----------------------|---------------|
|                                admin |                     1 |    ADMIN_USER |
|                                admin |                     2 | STANDARD_USER |
|                                admin |                (null) | NEW_CANDIDATE |
|                                admin |                     4 | HR_PERMISSION |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search