Group by and max is what you want.
HLGEM
2010-09-20 18:07:50
views:
22answers:
2I don't see how group by and/or max is going to do any good here. What do I group by here? genre.id? lose the authors. author.id? lose the genres. max will only return one out of a group, which has now already lost some data.
colinta
2010-10-27 17:55:41
(back after testing) I tried a bunch of combinations of GROUP BY, even threw in some MAX()'s, though that was half-hearted I'll admit. I didn't get the "whif" of progress...
colinta
2010-10-27 18:19:46
A:
Your second result set seems wrong:
1 | Dune: The… | 1 | Military | 1 | Brian Herbert
1 | Dune: The… | 4 | Sci-Fi | 2 | Kevin J Anderson
Can hardly mean what you want. Try wording the resultset how you want it, and you will probably be able to construct the statement you are looking for. Unless you can word it properly, people will have a hard time answering this question, or at least I will.
Martijn
2010-09-20 18:15:21
I've double checked this, the wording is correct (or at least appropriate). I've got three groups of data coming back: the books (2), the genres (4 on book one, 3 on book two), and authors (two for each book)the result set I WANT back (and herein lies my question) only contains 7 rows, not 14. Consider the case when an item has 20 "categories" and 10 "genres" doing two many-to-many joins will result in 200 rows. Is there a solution that returns only 20 rows (since that's the minimum needed to display ALL the categories, and the genres would be repeated)
colinta
2010-10-27 17:57:12
To me, the above result set means: There is a book called Dune: The..., which is about the military when written by Brian Herbert, and is about Sci-Fi when written by Kevin J Anderson. Why is Military here on the same row as Brian Herbert, and Sci-Fi on the same row as Kevin J anderson?
Martijn
2010-10-28 14:36:56