SQL to fetch data using column values from one table as columns of another table

Question

I have these two tables:

Org_Extra_Attr

org_id      attr_name       attr_path
1           desk_name       str1
1           citizen         bool1           
2           perm_user       bool1           
3           skype_id        str1            
3           twitter         str2

User_Attr_Values

org_id      user_id     str1    str2    str3    str4    bool1   bool2   bool3   bool4
1           1           b1d07   null    null    null    1       null    null    null
1           2           b2d01   null    null    null    0       null    null    null
2           3           null    null    null    null    1       null    null    null
2           4           null    null    null    null    1       null    null    null
3           5           sam_sky sam_twt null    null    null    null    null    null
3           6           tom_sky tom_twt null    null    null    null    null    null

So, the thing here is each org.can define max.of 4 extra attributes of type String and Boolean each, the Org_Extra_Attr table is like meta-data. For example org_id 1 has defined desk_name which will be str1's value for its users, whereas org_id 3 has skype_id which will be str1's value for its users.

This might be a bad design, but for now, I need to get users attribute names and values for a given org_id. Like for org_id = 1, I need a SQL query result(not a third table) like below:

user_id     attr_name   val
1           desk_name   b1d07
1           citizen     1
2           desk_name   b2d01
2           citizen     0

For org_id = 3

user_id     attr_name   val
5           skype_id    sam_sky
5           twitter     sam_twt
6           skype_id    tom_sky
6           twitter     tom_twt

user4219031 · Accepted Answer · 2018-02-01 10:38:06Z

1

Something like this

select 
t2.user_id,
t1.attr_name,
    CASE
    WHEN t1.attr_path='str1' then t2.str1
    WHEN t1.attr_path='str2' then t2.str2
    WHEN t1.attr_path='str3' then t2.str3
    WHEN t1.attr_path='str4' then t2.str4
    WHEN t1.attr_path='bool1' then t2.bool1
    WHEN t1.attr_path='bool2' then t2.bool2
    WHEN t1.attr_path='bool3' then t2.bool3
    WHEN t1.attr_path='bool4' then t2.bool4
END attr_value
FROM org_Extra_attr t1 inner join User_Attr_Values t2
on t1.org_id = t2.org_id
where t1.org_id=1

answered Feb 1, 2018 at 10:38

user4219031

4072 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aleksej Over a year ago

If the bool columns are numbers, you may need to add a to_char

Thorsten Kettner · Accepted Answer · 2018-02-01 11:00:21Z

This is not an answer, but too long for a comment

That is a bad data model. You should not store column names in your tables.

What you could have instead is:

Org_Attr (PK = org_id + attr_no)

org_id   attr_no   attr_name   type
1        1         desk_name   STRING
1        2         citizen     BOOL
2        1         perm_user   BOOL
3        1         skype_id    STRING
3        2         twitter     STRING

Org_Attr_User (PK = org_id + attr_no + user_id)

org_id   attr_no   user_id   value
1        1         1         b1d07   
1        1         2         b2d01   
1        2         1         1
1        2         2         0
2        1         3         1
2        1         4         1
3        1         5         sam_sky    
3        1         6         tom_sky   
3        2         5         sam_twt   
3        2         6         tom_twt

With such a model data integrity would be guaranteed and querying, too, would be simple:

select oau.user_id, oa.attr_name, oau.value
from Org_Attr oa
join Org_Attr_User oau using (org_id, attr_no)
where org_id = 1;

Collectives™ on Stack Overflow

SQL to fetch data using column values from one table as columns of another table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related