>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?
Sure you can, but you might want to read up on your SQL.
>Please apologize my limited knowledge of JOINS.
If you take a look at the MySQL documentation, you can see
there's a clear way of creating SQL statements.
[group by clause]
[order by clause]
( I'm not using the correct notation here, but some of these
are optional )
Now, a FROM clause can consist of multiple tables, including
multiple JOINs, each JOIN is following by a join-match-clause
(which is the ON part of the JOIN).
FROM myfirsttable t1 JOIN mysecondtable t2 ON t1.id = t2.foreignid
JOIN mythirdtable t3 ON t1.id = t3.id
etc... This will establish how these tables relate to eachother.
In the WHERE clause, you will write your row filtering items, eg:
WHERE t1.myuserid = 5
AND t2.mystatus = 'CONFIRMED'
Now, try and figure out your own SQL statement :-)
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!