June 10, 2005 at 1:20 am
Hello
I have 6 tables I would like to join and find unique instances of a certain type.
genre_parent
book_genre
book_rating
book_catalogue
author_genre
author
A book can belong to many genres but there is a hierachy of genres outlined in genre_parent where the id for the genre is the same as the foreign key to distinguish it as the parent.
So in genre_parent is
id 123
fk 234
id 474
fk 333
id 888
fk 888
id 888
fk 333
where id 888 would be the parent of 333 but is a parent because it has the same fk.
In book_genre is a table of all the genres, both parents and children. author_genre has all the genres an author can belong to, both parents and children again.
I need to select a distinct author who meets certain criteria in the ratings table but only select an author in their parent genre.
SELECT DISTINCT TOP 17
a.AUTHOR_ID, a.FIRST_NAME, a.LAST_NAME, r.RATING, gp.PARENT_GENRE
FROM AUTHORS a
INNER JOIN
AUTHOR_GENRE ag ON a.AUTHOR_ID = ag.AUTHOR_ID
INNER JOIN
GENRE_PARENT gp ON gp.PARENT_ID = ag.GENRE_ID
INNER JOIN
RATING r ON a.AUTHOR_ID = r.AUTHOR_ID
LEFT OUTER JOIN
BOOK_CATALOGUE bc ON a.AUTHOR_ID = bc.AUTHOR_ID
WHERE (gp.PARENT_ID = 888) and (gp.GENRE_ID = 888)and (a.FIRST_NAME IS NOT NULL) AND (r.RATING > 0) AND (c.AUTHOR_ID IS NOT NULL) AND
ORDER BY r.RATING
Yet I am still getting authors that aren't in the 888 parent category, but are part of the category as a child.
Any help is much appreciated.
Thanks
June 10, 2005 at 3:07 am
What's the schema of the GENRE_PARENT table? ...There are references to gp.PARENT_GENRE and gp.PARENT_ID. Is there more logic after the last "AND" but before "ORDER BY"?
Regardless, try putting gp.Parent_ID or other columns in the select list for debugging purposes.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply