Thank you for the clarification!
For some reason I believed the WHERE belonged to the specific JOIN
clause.
I came up with a clause, but I removed the Group part, for I didn't know
how to do that.
I will work on that later.
Would this statement be good SQL practice?
-------------------------------
CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL
SECURITY DEFINER VIEW `shared_v`
AS select `shared`.`ID` AS `ID`,`shared`.`RawID` AS
`RawID`,`shared`.`Added` AS `Added`,
`shared`.`Keywords` AS `Keywords`,`shared`.`OwnerID` AS
`OwnerID`,`shared`.`UserID` AS `UserID`
FROM (`shared`
LEFT JOIN `users` on((`shared`.`UserID` = `users`.`ID`)))
WHERE (`users`.`Name` = convert(substring_index(user(),_utf8'@',1) using
latin1))
OR (`Shared`.`OwnerID` = (SELECT ID FROM `Users` WHERE `Name` =
convert(substring_index(user(),_utf8'@',1) using latin1)));
-------------------------------
-----Original Message-----
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 12:09 PM
To: Andreas Iwanowski
Cc: mysql@lists.mysql.com
Subject: Re: View with Subselect for User ID
Hello Andreas,
>I tried the following statement:
>------------
>CREATE OR REPLACE ALGORITHM=UNDEFINED [EMAIL PROTECTED] 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.
http://www.w3schools.com/sql/default.asp
>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.
Basically:
[select clause]
[from clause]
[where clause]
[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 :-)
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
