combining the following two SQL queries

[carpeliam]

I'd like to combine the following two SQL queries into one query, but I can't seem to manage it in MySQL. Might I be missing something?


SELECT NewsArticle_Role.RoleID FROM NewsArticle_Role, Role WHERE NewsArticle_Role.NewsArticleID = 1 AND NewsArticle_Role.RoleID = Role.RoleID AND Role.Name = 'AUTHOR'

[ assign the value of the above to the value 'XXX' ]

SELECT User.* FROM User, User_Role, NewsArticle_Role, Role WHERE User_Role.UserID = User.UserID AND User_Role.RoleID = XXX


I think the system will hopefully be somewhat self-explanatory... there is a role table that describes roles for the whole system, and relationship tables that relate these roles to their individual tasks (like NewsArticle_Role) as well as a relationship table that relates the role to the user (User_Role). In this particular example, I want to find the user who is the author of a particular news article.

If you have any suggestions, I'd greatly appreciate them. Liam Morley
lmorley@wpi.edu
"light the deep, and bring silence to the world.
light the world, and bring depth to the silence."

 

[Broccoli2]

Have you tried something like this?

SELECT User.*
FROM NewsArticle_Role
INNER JOIN Role ON NewsArticle_Role.RoleID=Role.RoleID
INNER JOIN User_Role ON Role.RoleID=User_Role.RoleID
INNER JOIN User ON User_Role.UserID=User.UserID
WHERE NewsArticle_Role.NewsArticleID=1
AND Role.Name='AUTHOR';

-Rob

 

[carpeliam]

It turns out that I was just extremely exhausted at the time of this post, and couldn't code for the life of me... I spent about two hours cleaning up bad code ) let that be a lesson to all, never code when you're tired... <p>Liam Morley<br><A HREF="mailto:"></A><br>&quot;light the deep, and bring silence to the world.<br>light the world, and bring depth to the silence.&quot;