CODEWITHSUNDEEP
sqljoinamazon-redshift
Shivam
Shivam

Reputation: 243

How to join two tables based on some condition in sql?

I need help to join two tables to get output. I tell you the scenario. Could you please help me.

Example:

I have one sql query: Query :

SELECT * 
FROM (
  SELECT schemaname ,objectname,usename,
         HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS sel,
         HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ins,
         HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS upd,
         HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS del,
         HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AND has_schema_privilege(usrs.usename, schemaname, 'usage')  AS ref 
  FROM (
     SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj 
     FROM pg_tables 
     WHERE schemaname not in ('pg_internal') 
     UNION 
     SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj 
     FROM pg_views 
     WHERE schemaname not in ('pg_internal')
  ) AS objs,
  (SELECT * FROM pg_user) AS usrs ORDER BY fullobj
) 
WHERE (sel = true or ins = true or upd = true or del = true or ref = true) 
   and schemaname='medaff' 
   and usename not in ('rdsdb','clustersa','prdrscl01master') 
   and objectname in RES;

The above query is giving some output:

Table 1

schemaname  objectname   usename           sel  ins  upd  del  ref
medaff      dmn_category medaff_dev_admin  True True True True True
medaff      dmn_category emea_dev_admin    True True True True True
medaff      dmn_category cdeadmin          True True True True True

I have other table which have some records :

Table 2:

application_name  tablename
smart_source      dmn_category

How to join these two tables to get the application name also in the final output:

application_name schemaname  objectname   usename           sel  ins  upd  del  ref  
smart_source     medaff      dmn_category medaff_dev_admin  True True True True True
smart_source     medaff      dmn_category emea_dev_admin    True True True True True
smart_source     medaff      dmn_category cdeadmin          True True True True True

Upvotes: 1

Views: 109

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Your query looks fine. UNION should be UNION ALL, though, and

) AS objs,
(SELECT * FROM pg_user) AS usrs

should be

) AS objs
CROSS JOIN pg_user AS usrs

and

ORDER BY fullobj

is superfluous, because the DBMS is allowed to ignore ORDER BY inside subqueries. (If you want your results ordered, then place ORDER BY fullobj at the end of your query.)

But anyway, all you seem to want is to join the table2:

) AS objs
CROSS JOIN pg_user AS usrs
INNER JOIN table2 ON table2.tablename = objs.objectname

The complete query:

SELECT * FROM 
(
  SELECT
    table2.application_name,
    objs.schemaname,
    objs.objectname,
    usrs.usename,
    HAS_TABLE_PRIVILEGE(usrs.usename, objs.fullobj, 'select') AND has_schema_privilege(usrs.usename, objs.schemaname, 'usage') AS sel,
    HAS_TABLE_PRIVILEGE(usrs.usename, objs.fullobj, 'insert') AND has_schema_privilege(usrs.usename, objs.schemaname, 'usage') AS ins,
    HAS_TABLE_PRIVILEGE(usrs.usename, objs.fullobj, 'update') AND has_schema_privilege(usrs.usename, objs.schemaname, 'usage')  AS upd,
    HAS_TABLE_PRIVILEGE(usrs.usename, objs.fullobj, 'delete') AND has_schema_privilege(usrs.usename, objs.schemaname, 'usage')  AS del,
    HAS_TABLE_PRIVILEGE(usrs.usename, objs.fullobj, 'references') AND has_schema_privilege(usrs.usename, objs.schemaname, 'usage') AS ref
  FROM
  (
    SELECT 
      schemaname, 
      't' AS obj_type, 
      tablename AS objectname, 
      schemaname + '.' + tablename AS fullobj
    FROM pg_tables 
    WHERE schemaname not in ('pg_internal') 
    UNION ALL
    SELECT 
      schemaname, 
      'v' AS obj_type, 
      viewname AS objectname, 
      schemaname + '.' + viewname AS fullobj 
    FROM pg_views 
    WHERE schemaname not in ('pg_internal')
  ) AS objs
  CROSS JOIN pg_user AS usrs
  INNER JOIN table2 ON table2.tablename = objs.objectname
)
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
  and schemaname = 'medaff' 
  and usename not in ('rdsdb', 'clustersa', 'prdrscl01master')
ORDER BY schemaname, objectname, application_name, usename;

Upvotes: 0

Rubs Bieira
Rubs Bieira

Reputation: 268

When you want to represent 2 tables you can use inner join on but for this you need to have something in commun in each table. For example :

select t.name, o.code from FirstTable t inner join OtherTable o on t.code = o.code

Upvotes: 1

Kassian Sun
Kassian Sun

Reputation: 799

I think the point here is you want to join a complex query result with another table? You can achieve it by:

  1. Using WITH tbl AS (.....) then join the other table on tbl(query result).
  2. Creating a VIEW for your first result, then join the other table with this view.

Upvotes: 0

Related Questions