Steve Manes wrote:
Without seeing the actual SQL query it's impossible to guess where the
problem is. In general though, cartesian joins are the result of
incomplete join conditionals and, sometimes, overly-complex WHERE
clauses in cases where a left join or sub-select would be more reliable.
The classic cartesian join:
SELECT * FROM users, orders;
OK, here is what I posted on the NYPHP MySQL list several days ago. That
list is by far not as active as this one. In the meantime I came across
VIEWs, which seem to work in the end the same as temporary tables. I yet
have to figure out why a VIEW is considered as good and a temp table as
evil.
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
...and also this....
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 Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
