Sql: Selecting Columns Based On Column Value From Another Table

I have the following tables: UserPrivileges: +--------+------+------+------+ | UserID | Col1 | Col2 | Col3 | +--------+------+------+------+ | 1 | 0 | 1 | 1 | |

Solution 1:

The answer depends upon your requirements for the result. Do you require a result with a consistent set of columns, regardless of user privs? If so, you could set the disallowed values to null (or some other special value) using a IF clause, e.g.,

SELECT IF (p.col1 = 0 THEN NULLELSE d.col1) AS col1, 
       IF (p.col2 = 0 THEN NULLELSE d.col2) AS col2,
       IF (p.col3 = 0 THEN NULLELSE d.col3) AS col3
FROM Data d, 
     UserPrivileges p
WHERE p.userId = '#'AND d.DataId = '#'

Of course, the "special value" could be a problem, since you need a value that would never appear in the data. If you needed to know that difference between a null because the real value is null vs. null because it is a prohibited column then you can't use null.

Another approach would have you simple include the privilege indicator for each column appear in the result, and let your business logic use that to determine which values are visible to the user.

A very different approach would have the result set to contain only the allowed columns. In this case you'll need to build your sql statement dynamically. I don't know if you are doing this in a stored procedure or in a host language, but the basic idea is something like this:

string sqlCmd = "SELECT " 
    + (SELECT (FIELDS_NAME_QUERY(UserID='#') 
       FROM USER_PRIVILEGES 
       WHERE userid='#') 
    +FROM data d 
execute sqlCmd

"execute" meaning whatever you have available to execute a string as a sql command.

---

more after clarification by OP:

Ok, you need sql function that returns a string that looks like "colname1, colname2, ...". The following resembles what it would look like in sql server. syntax

createfunction   
FIELDS_NAME_QUERY (@useridint)  
beginselect col1, col2, col3... INTO@col1priv, @col2priv, @col3privFROM userPrivileges WHERE UserId =@UserIddeclare@result varhcar(60)  
set@result=''  
if (@col1priv=1) @result='col1'  
if (@col2priv=1) @result=@result+' ,col2'  
if (@col3priv=1) @result=@result+' ,col3'return@resultend

Solution 2:

have not tried it, but something like this should work

SELECT (SHOW COLUMNS FROMtableWHERE expr) FROM data WHERE DataID ='#'

Check this post for details - How can I get column names from a table in Oracle?

Let us know how you solve this...