List:General Discussion« Previous MessageNext Message »
From:Andreas Iwanowski Date:April 25 2007 1:09am
Subject:RE: View with Subselect for User ID
View as plain text  
 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 DEFINER=`root`@`localhost` 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:m.tonies@stripped] 
Sent: Tuesday, April 24, 2007 12:09 PM
To: Andreas Iwanowski
Cc: mysql@stripped
Subject: Re: View with Subselect for User ID

Hello Andreas,

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


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