SQL: selecting columns based on a column value from another table

Question

I have the following tables:

UserPrivileges: +--------+------+------+------+ | UserID | Col1 | Col2 | Col3 | +--------+------+------+------+ | 1 | 0 | 1 | 1 | | 2 | 0 | 0 | 1 | | 3 | 1 | 0 | 0 | | 4 | 1 | 1 | 0 | +--------+------+------+------+ Data: +--------+------+------+------+ | DataID | Col1 | Col2 | Col3 | +--------+------+------+------+ | 1 | A | B | C | | 2 | D | E | F | | 3 | G | H | I | | 4 | J | K | L | +--------+------+------+------+ 

My question in its simplest form has nothing to do with the Data table, but I just explain it so that I can do it wrong.

How to select column names from UserPrivileges based on value? So that I can use the result in another query, select only those columns.

Something like that:

SELECT (COLUMNS_NAME_QUERY_FROM_UserPrivileges(UserID='#')) WHERE DataID = '#' FROM Data

Or I'm not against the best way to manage user privileges for specific columns.

Answer 1

The answer depends on your requirements for the result. Do you need a consistent column result regardless of user privileges? If so, you can set invalid values ​​to null (or some other special value) using the IF clause, for example,

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

Of course, “special meaning” can be a problem, because you need a value that will never be displayed in the data. If you need to know this difference between a null value, because the real value is null or null, because it is a forbidden column, you cannot use null.

In another approach, it will be easier for you to include a privilege indicator for each column in the result, and let your business logic use this to determine which values ​​are visible to the user.

A completely different approach would be to have a result set containing only valid columns. In this case, you will need to build your SQL query dynamically. I don't know if you are doing this in a stored procedure or in the 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" means what you have to execute the string as a sql command.

---

more after clarification OP:

Ok, you need a sql function that returns a string that looks like "colname1, colname2, ...". The following is similar to what it would look like on sql server. syntax
create function
FIELDS_NAME_QUERY (@userid int)
begin
select col1, col2, col3... INTO @col1priv, @col2priv, @col3priv FROM userPrivileges WHERE UserId = @UserId
declare @result varhcar(60)
set @result = ''
if (@col1priv = 1) @result = 'col1'
if (@col2priv = 1) @result = @result + ' ,col2'
if (@col3priv = 1) @result = @result + ' ,col3'
return @result
end

Answer 2

didn't try but something like this should work

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

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

Let us know how you solve this ...