On 4/23/07, Andreas Iwanowski <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
