List:General Discussion« Previous MessageNext Message »
From:Christian High Date:April 24 2007 11:57am
Subject:Re: View with Subselect for User ID
View as plain text  
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
Thread
View with Subselect for User IDAndreas Iwanowski24 Apr
  • Re: View with Subselect for User IDChristian High24 Apr
RE: View with Subselect for User IDAndreas Iwanowski24 Apr
  • Re: View with Subselect for User IDMartijn Tonies24 Apr
RE: View with Subselect for User IDAndreas Iwanowski25 Apr