List:General Discussion« Previous MessageNext Message »
From:Andreas Iwanowski Date:April 24 2007 3:38pm
Subject:RE: View with Subselect for User ID
View as plain text  
Hello Christian,

Thank you very much for this reply. It was very helpful, and the user
matching part works as it should.

Is there any way to JOIN on two tables, so I can match the Users.GroupID
field against a JOIN on Groups.ID?
Also, I tried adding a WHERE clause after the join to compare
Users.UserID to Shared.OwnerID, but that produced an error.

Maybe that is confusing, but basically I wish have the user only see
data where:
1. His ID is in the UserID field of the row OR
2. His ID is in the OwnerID field of the row OR
3. His data from the Users table references an ID in the Groups table
that is is the Shared table's GroupID field

I tried the following statement:
------------
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `shared_v` AS SELECT `shared`.`ID` AS
`ID`,`shared`.`RawID` AS `RawID`,`shared`.`OwnerID` AS
`OwnerID`,`shared`.`UserID` AS `UserID`,`shared`.`GroupID` AS `GroupID`
from `shared` JOIN Users ON (Shared.UserID = Users.ID) WHERE Users.Name
= (convert(substring_index(user(),_utf8'@', 1) using latin1)) AND JOIN
Groups ON (Shared.GroupID = Groups.ID) WHERE Groups.ID = Users.GroupID)
WHERE Users.ID = Shared.OwnerID;
------------

That failed with multiple errors, the first one occuring at the AND
JOIN. Apparently I cannot have multiple JOINS in one statement?
Please apologize my limited knowledge of JOINS.

I would appreciate if you could take the time to have a look at this.
Thank you very much!,
  Andy




>>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

> 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
>
>



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