List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 24 2007 4:08pm
Subject:Re: View with Subselect for User ID
View as plain text  
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