Need help writing a query

Hi I am trying to get the results for each of the following queries into one query. I know I can do a union but I was wondering if there is a better way because each table has different columns? Thanks

select distinct RESOURCE_ID from USER_ROLES where TEST_IND ='Y'

select distinct RESOURCE_ID from USER_PLATFORMS where TEST_IND ='Y'

select distinct RESOURCE_ID from USER_COMPETENCIES where TEST_IND ='Y'

select distinct RESOURCE_ID from USER_TECH_SKILLS where TEST_IND ='Y'

select distinct RESOURCE_ID from USER_MGR_SKILLS where TEST_IND ='Y'

select distinct RESOURCE_ID from USER_APPS where TEST_IND ='Y'

select distinct RESOURCE_ID from BUS_PROCS where TEST_IND ='Y'

Answers


Although you say you don't want a UNION query, nothing in your requirements as described in the comments precludes it. It is okay if the tables have different columns as long as the columns you select are the same across all the tables.

This is what you need

select RESOURCE_ID from USER_ROLES where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_PLATFORMS where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_COMPETENCIES where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_TECH_SKILLS where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_MGR_SKILLS where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_APPS where TEST_IND ='Y'
UNION
select RESOURCE_ID from BUS_PROCS where TEST_IND ='Y'

Note: I took out the distinct clauses, the union will make the result distinct anyway so it is superfluous.


UNION is your best option here, it will automatically remove duplicates from the result set, if you want to keep the duplicated you can use UNION ALL.

select RESOURCE_ID from USER_ROLES where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_PLATFORMS where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_COMPETENCIES where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_TECH_SKILLS where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_MGR_SKILLS where TEST_IND ='Y'
UNION
select RESOURCE_ID from USER_APPS where TEST_IND ='Y'
UNION
select RESOURCE_ID from BUS_PROCS where TEST_IND ='Y'

UNION MSDN


Other than the unions already mentioned, you could use a multi-statement table-valued function: define a table variable and SELECT INTO from your multitude of queries, then select * from functionName(). You would still need each query to produce the same columns with the same data types, however, so you may need to cast/convert the round pegs into the square, columnar holes.


Need Your Help

Nginx Lua Scripting

nginx lua

One of the really cool things about Nginx is that you can take control of what it does by injecting Lua script at various phases of request processing. I have successfully used the rewrite_by_lua/...

Avoid using predefined css div while integration

css html integration

I have defined div in css file as follows: