1 2 Previous Next 15 Replies Latest reply on Apr 1, 2019 10:50 AM by Twinkle Bansal

    Joins in Tableau

    Twinkle Bansal

      HI ALL,

       

      Need Urgent help in below Query!!!! Actually table '_users' is not having direct join with table '_projects' so I joined that one with table 'sites' and wrote below query. But again and again am getting error 'Invalid FROM Clause near table _users':

       

      select  "_users"."id",

      "_projects"."name",

      FROM

      "public"."_users",

      "public"."_projects"

      INNER JOIN "public"."sites" ON ("_users"."site_id"="sites"."id")

      INNER JOIN  "public"."_projects" ON ("_projects"."site_id"="sites"."id")

        • 1. Re: Joins in Tableau
          arvindgarg

          Below is corrected SQL. 

           

          select  "_users"."id", "_projects"."name"

          from "public"."_users" INNER JOIN "public"."sites" ON ("_users"."site_id"="sites"."id")

          INNER join  "public"."_projects"  ON ("_projects"."site_id"="sites"."id")

          1 of 1 people found this helpful
          • 2. Re: Joins in Tableau
            arvindgarg

            On a second thought, what do you want to achieve using this SQL.

             

            A site has projects

            A site has users

             

            All the users on site may or may not have access to all the projects on the site.

            • 3. Re: Joins in Tableau
              Twinkle Bansal

              Thanks Arvind i tried and did same but getting error:

               

              Invalid reference to 'FROM Clause' for table '_projects'.

              • 4. Re: Joins in Tableau
                arvindgarg

                I ran same sql on my database. I get not error.

                CAn you share any screenshot?

                • 5. Re: Joins in Tableau
                  Ethan Lang

                  Twinkle,

                   

                  What environment are you using Oracle or Microsoft?

                   

                  in You SQL above you have a “,” behind the last value in your select statement. That might be the error you are getting.

                  • 6. Re: Joins in Tableau
                    Twinkle Bansal

                    thanks Ethan, am using Posgresql

                    • 7. Re: Joins in Tableau
                      Twinkle Bansal

                      Thanks Arvind i was able to run the sql. And yes all users may or may not have access to particular project, hence requirement is to see users who have access to particular project and respective groups. By using above query  am able to display projects name and users name, but user's name are coming w.r.t site but not w.r.t project. Can you pls guide how to achieve that?

                      • 8. Re: Joins in Tableau
                        arvindgarg

                        Permission is a complex topic. User may have permssion due to different things.

                         

                        Please look at below sql. If this not perfect, It will give you correct direction at least.

                         

                        select distinct

                        su.name as "user_name",

                        su.friendly_name as "friendly_name",

                        p.id as "project_id",

                        p.name as "project_name",

                        si.name as "site_name",

                        'User' as "User_Group"

                        from next_gen_permissions n inner join  capabilities c on c.id= n.capability_id

                        inner join projects p on p.id = n.authorizable_id

                        inner join sites si on si.id = p.site_id

                        inner join users u on u.id = n.grantee_id

                        inner join system_users su on su.id = u.system_user_id

                        where n.authorizable_type = 'Project' and n.grantee_type = 'User'      and n.permission =3

                         

                         

                        union all

                         

                         

                        select distinct

                        su.name as "user_name",

                        su.friendly_name as "friendly_name",

                        p.id as "project_id",

                        p.name as "project_name",

                        si.name as "site_name" ,

                        'Group' as "User_Group"

                        from next_gen_permissions n inner join  capabilities c on c.id= n.capability_id

                        inner join projects p on p.id = n.authorizable_id

                        inner join sites si on si.id = p.site_id

                        inner join groups g on g.id = n.grantee_id

                        inner join group_users gu on g.id = gu.group_id 

                        inner join users u on u.id = gu.user_id

                        inner join system_users su on su.id = u.system_user_id

                        where n.authorizable_type = 'Project' and n.grantee_type = 'Group' and n.permission =1

                        1 of 1 people found this helpful
                        • 9. Re: Joins in Tableau
                          Twinkle Bansal

                          Thank you Arvind, it reallly helped me its now bringing users with respect to project. Am really thankful to you

                           

                           

                          Just wanted to understand below conditions as not sure how its working:

                           

                          where n.authorizable_type = 'Project' and n.grantee_type = 'User'      and n.permission =3

                          where n.authorizable_type = 'Project' and n.grantee_type = 'Group' and n.permission =1

                          • 10. Re: Joins in Tableau
                            arvindgarg

                            I would suggest you to refer this URL. It is data dictironary for workgroup database.

                             

                             

                            Workgroups Database - Tableau

                            1 of 1 people found this helpful
                            • 11. Re: Joins in Tableau
                              Twinkle Bansal

                              Thank you will stay in touch with you for more guidance.

                              • 12. Re: Joins in Tableau
                                arvindgarg

                                Glad it worked out for you.

                                Please close this thread by marking correct answer.

                                1 of 1 people found this helpful
                                • 13. Re: Joins in Tableau
                                  Twinkle Bansal

                                  Thank you Arvind. Actually one more doubt, actually it really worked well to get users and groups as per individual projects, can we get user groups as per site also?

                                  As its displaying the groups w.r.t project but if we want to display users w.r.t site groups, is that possible in same query?

                                  Please suggest.

                                  • 14. Re: Joins in Tableau
                                    arvindgarg

                                    This query should give you group name as well.

                                     

                                    select distinct

                                    su.name as "user_name",

                                    g.name as "group_name",

                                    su.friendly_name as "friendly_name",

                                    p.id as "project_id",

                                    p.name as "project_name",

                                    si.name as "site_name",

                                    'User' as "User_Group"

                                    from next_gen_permissions n inner join  capabilities c on c.id= n.capability_id

                                    inner join projects p on p.id = n.authorizable_id

                                    inner join sites si on si.id = p.site_id

                                    inner join users u on u.id = n.grantee_id

                                    inner join system_users su on su.id = u.system_user_id

                                    inner join group_users gu on u.id = gu.user_id

                                    inner join groups g on g.id = gu.group_id

                                    where n.authorizable_type = 'Project' and n.grantee_type = 'User'      and n.permission =3

                                    union all

                                    select distinct

                                    su.name as "user_name",

                                    g.name as "group_name",

                                    su.friendly_name as "friendly_name",

                                    p.id as "project_id",

                                    p.name as "project_name",

                                    si.name as "site_name" ,

                                    'Group' as "User_Group"

                                    from next_gen_permissions n inner join  capabilities c on c.id= n.capability_id

                                    inner join projects p on p.id = n.authorizable_id

                                    inner join sites si on si.id = p.site_id

                                    inner join groups g on g.id = n.grantee_id

                                    inner join group_users gu on g.id = gu.group_id

                                    inner join users u on u.id = gu.user_id

                                    inner join system_users su on su.id = u.system_user_id

                                    where n.authorizable_type = 'Project' and n.grantee_type = 'Group' and n.permission =1

                                    1 2 Previous Next