Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type =
'Project' LEFT OUTER JOIN posts ON posts.forum_id =
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT OUTER
JOIN forums forums_titles ON forums_titles.work_id =
titles.id AND forums_titles.work_type = 'Title' LEFT
OUTER JOIN posts posts_forums ON posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
I guess I should have given more background....
> > I can't just the left outer join part as it's
> > autogenerated but I can change the WHERE clause
>
> Autogenerated by what, exactly? Does it run at all
> the way it is? That
> is quite a monster SELECT statement. In fact, if it
> doesn't run now,
> perhaps all it requires is a good jolt of
> electricity.
>
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=>["(readerships.read !=
'1' OR readerships_topics.read != '1')"], :order=>nil,
:include=>{:forum=>{:posts=>{:topic=>:readerships}}},
{:titles=>{:forum=>{:posts=>{:topic=>:readerships}}}}}
> > I'm not good a sql syntax, but I would love to
> > learn....
>
> Are you reasonably certain that your SELECT requires
> all that? Could
> your application maybe be adjusted so that you could
> break that up a little?
>
The problem I have is that I have a projects that is
linked to a forum table (itself linked with posts and
topics) and a titles table that is also linked to a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager designs
the database schema based on what the customer wants
:-( ) and which projects have posts that have been
read by the user.
Thanks
Thomas
_____________________________________________________________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
