I suggest you break this down into sub-queries. Get that to work, then you can play around with using joins. Makes a lot more logical sense when you see how the sub-queries are working. (in reality I think even Joins end up being converted to sub-queries in the bowls of the MySQL engine).
http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html :-) ed PS... GREAT BOOK for this kind of stuff: MySQL Crash Course, Ben Forta http://www.amazon.com/MySQL-Crash-Course-Yourself-Minutes/dp/0672327120/ref=sr_1_9/104-1193578-7671156?ie=UTF8&s=books&qid=1185030010&sr=8-9 On 7/21/07, David Krings <[EMAIL PROTECTED]> wrote:
> Thanks for the reply. The only common field is the CourseID field. It is > present in all four tables, has the same type (integer), and is to be in > all cases 23 (or whatever the ID of the desired course is). My guess was > that since I limit cupssbmain.CourseID to be 23 and ask for all other > CourseID fields in the remaining tables to be equal to > cupssbmain.CourseID that this would be sufficient. > > The tables have these columns (and a few others unrelated to this issue): > > cupssbmain: StoryboardID, PageID, LessonID, ModuleID, CourseID > cupspages: PageID, LessonID, ModuleID, CourseID, Page_Position > cupslessons: LessonID, ModuleID, CourseID, Lesson_Position > cupsmodules: ModuleID, CourseID, Module_Position > > Any advice on how to craft something better out of this? I am at a total > loss. :( > > David Hmmm, came across VIEWs while going through yet another somewhat useless JOIN tutorial (they generally cover joining two tables, which is trivial even for me). Do I understand it correctly that a VIEW is similar to a temporary table without being a table per sé? Can I use VIEWs in exactly the same way as I would use a table? I personally like temp tables, after all they are available and why not use them, but Joe Celko will knock down my door and strangle me. Although, in his view anyone is a slacking idiot who doesn't do it as he mandates. I also thought about changing Page_Position or adding a field for having a consecutive numbering of all pages within a course. The problem with that is that it will add more complexity when adding/moving/deleting modules, lessons, and pages. Besides that, combining the position fields already does give me that information. Unless someone thinks using a VIEW is foolish I will tinker around with that and see if I can create a single field out of the three position fields, which obviously will make sorting on it much easier. And VIEWs are excplicitly allowed under Celko. David _______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
-- the Blog: http://www.utopiaparkway.com the Karma: http://www.coderswithconscience.com the Projects: http://flickr.com/photos/[EMAIL PROTECTED]/ the Store: http://astore.amazon.com/httpwwwutopic-20 _______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
