Also you might want to take a look at http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
This is one of a series of mysql tutorial pages, this one is about joins. -------------------------------------------------------- This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Carlos A Hoyos Sent: Friday, July 20, 2007 1:55 PM To: MySQL SIG Subject: Re: [mysql] JOINing four tables, but how? > 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 _______________________________________________ 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 _______________________________________________ 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
