NYCPHP Meetup

[David Krings]

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