List:General Discussion« Previous MessageNext Message »
From:Andreas Iwanowski Date:April 24 2007 2:40am
Subject:View with Subselect for User ID
View as plain text  
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
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