For some reason I feel this is a very easy task but I just can't get my head around it. I have two tables. Table A is list of companies called company and Table B is a list of users called users. The user table has a filed called 'access' which stores the ids of all the companies that a user is allowed to see.
companies
id name
-- ----
1 coca cola
2 samsung
3 apple
4 microsoft
users
id access name
-- ------ ----
1 1,3,4 brain owen
2 2,3 janet smith
2 1,2,4 peter pete
2 2,3,4 jane dow
My problem is how do I display the list of users who have access to a particular company? eg coca cola. Thank you.
I think this will give you the user name of coca cola
select name from users where access in(1)
id name
-- ----
1 coca cola
2 samsung
3 apple
4 microsoft
userId companyId
------ ---------
1 1
1 3
1 4
2 2
2 3
id name
-- ----
1 brain owen
2 janet smith
Two users can't have the same ID...