Hello MySQL experts,
I am trying to create a view whose access is based on a User ID that
need to be looked up in a different table.
Here is an example of what I'm trying to do:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `shared_v` AS select `Shared`.`ID` AS
`ID`,`Shared`.`RawID` AS `RawID` FROM `Shared` WHERE (`Shared`.`UserID`
= (SELECT UserID FROM Users WHERE Login =
convert(substring_index(user(),_utf8'@',1) using latin1));
However, MySQL doesn't gulp the subquery for the ID.
I don't want to have a VARCHAR column with the user name in this table,
because it can easily
grow very large.
Would JOINS be the way to go?
If so, could anyone please give me an example of how to accomplish this
I would appreciate any tip.
Thank you in advance,