Hi!
I need some crash course in table joining. I tried several variations
and the closest one generates the right results as it seems, but not in
the right order and only by using DISTINCT.
OK, here is the situation. I want record IDs from a table called
cupssbmain and have those sorted based on position numbers located in
three additional tables cupsmodules, cupslessons, and cupspages. The
sorting on modules is to take precedence over the sorting on lessons and
that has precedence over the sorting of pages. Each table has a column
called CourseID and I want the sorted sbids for course 23.
My query looks currently like this:
SELECT DISTINCT cupssbmain.StoryboardID AS sbid,
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
There are a total of 26 rows returned and indeed those values in the
sbid column are the IDs that I expected, but not in the right order. The
right order would be 1,2,3,4....26 as I just added the records to
cupssbmain. I do get 23,11,24,12,25,13,26....
I tried to get some idea of what is going on by showing the fields with
the position numbers using this query:
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.
Obviosuly, I'm doing something wrong, but I have no clue what. I looked
for examples on how to join multiple tables, but they all show only how
to join two tables. I did try some things, but generally get syntax errors.
Can anyone help me? I could do the sorting in PHP, but I expect the code
to be somewhat ugly.
Thanks in advance,
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
