Carlos A Hoyos wrote:
SELECT DISTINCT cupssbmain.StoryboardID AS sbid,
cupsmodules.Module_Position,
cupslessons.Lesson_Position,
cupspages.Page_Position
FROM cupssbmain, cupsmodules, cupslessons, cupspages
WHERE cupssbmain.CourseID = 23
AND cupsmodules.CourseID = cupssbmain.CourseID
AND cupslessons.CourseID = cupssbmain.CourseID
AND cupspages.CourseID = cupssbmain.CourseID
ORDER BY cupsmodules.Module_Position ASC,
cupslessons.Lesson_Position ASC,
cupspages.Page_Position ASC
But that no longer gives me the desired 26 rows, but now out of a sudden
1248 rows showing all possible combinations of the sbid field with the
three position number fields.
Looks like you're missing a field to join, i.e. common fields between the
modules, lessons and pages tables.
It's hard to tell without knowing what your data model looks like, but your
second query is doing a cartesian product of all possible arrangements of:
cupssbmain.StoryboardID x cupsmodules.Module_Position x
cupslessons.Lesson_Position x cupspages.Page_Position
For all entries in either table with CourseID = 23. So, if each table had 5
rows with CourseID = 23, then the result set would have 5^4 = 625 rows.
Look for other fields that the cupsmodules, cupslessons and cupspages would
have in common, perhaps a field like StoryboardID?
Carlos Hoyos
Hi!
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
_______________________________________________
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
