tom wang wrote:
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'))
That's still, um ... a bit difficult to follow. First rule of SQL: line
breaks are allowed. Especially when posting queries in an email.
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've been waiting for a sign to show me that giving RoR a pass was the
correct thing to do. Now, i think i know ;-)
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.
The first thing that (i think) i see is that you're not selecting
anything at all from titles:
SELECT * FROM projects
I'm not sure i understand your schema enough to help. Are you sure you
need a separate titles table, for instance? And you shouldn't be able to
use the alias readerships_topics in the WHERE clause. Come to think of
it, your WHERE clause makes no sense at all because you're selecting
from projects. If you want to test readerships.read it should go in the
ON clause of that particular join:
LEFT OUTER JOIN readerships
ON readerships.topic_id = topics.id
AND readerships.read != '1'
Though i'm unsure whether or not the test on topics.id would work here,
either.
brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
