On 4/23/07, Andreas Iwanowski <namezero@stripped> wrote:
> 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
> with joins?
>
> I would appreciate any tip.
>
> Thank you in advance,
> -Andy
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
From what i can tell from your create statement I think a join will
get you what you want. Try this--
SELECT Shared.ID, Shared.RawID
FROM Shared
JOIN Users ON
(Shared.ID = Users.UserID)
WHERE Users.Login = convert(substring_index(user(),_utf8'@', 1) using latin1);
If that doesn't get you what you are looking for than i have
misunderstood your requirements
CJ
