I'm having a bit of trouble figuring out what to do here. Basically I have 3 tables shown below
ID Firstname Lastname etc.... 1 James Hay 2 Someone Else
PermissionID UserID 1 1 2 1 2 2 3 2
Permissions Table Decode
ID Name Description 1 Web Access This user can access the website 2 Uploader This user is able to upload 3 Book Reader This user can read books!
Basically what I'm doing first is SELECT * On the permissions decode table and then outputting that into a table on my page. What I then want to do is to get a list of users and all the rows in the permissions table for each user. In the example above you can see that the user with the id 1 has two rows in the permissions table, so he is 'allowed' for both of those thing (what they are can be found in the decode table). So is there a way perhaps to get all the ids from the Permissions table and create a column for each one in my result set?
Here is the end product I am looking for ie my result set. When I say dynamic I mean that as more is added to the decode table, more columns will show up in the query. There won't be alot though, theres 7 at the moment and it's not expected to grow much more than that.
Id(userid) Firstname Lastname Web Access Uploader Book Reader 1 James Hay 1 1 0 2 Someone Else 0 1 1
Thanks for your help, also suggestions to me finding a better way are welcome.