the_924 Posted October 16, 2006 Share Posted October 16, 2006 I have a situation with two tables:CREATE TABLE `quiz`( id int (5) NOT NULL auto_increment, title varchar(100) NOT NULL, course_id INT(4) NOT NULL, question_first_id INT(6) NOT NULL, question_last_id INT(6) NOT NULL, question_list text NOT NULL, PRIMARY KEY (id));andCREATE TABLE `course`( id int (5) NOT NULL auto_increment, name varchar(100) NOT NULL, description text NOT NULL, PRIMARY KEY (id));I'm trying to query the Quiz table and generate a table that would, ideally, look something like this:[table][tr][td][b]Quiz name [/b][/td] [td][b]Quiz course[/b][/td][/tr][tr][td]Lesson 1[/td][td]AP Euro History[/td][/tr][tr][td]Lesson 1[/td][td]AP US History[/td][/tr][tr][td]Fun quiz[/td][td] - NO COURSE! -[/td][/tr][/table]However, since I want the Course names, and not ID's, I need to "join" them together (I think); I want the query results to look like this:[table][tr][td][b]quiz.id [/b][/td] [td][b]quiz.name [/b][/td] [td][b]course.name [/b][/td][/tr][tr][td]1[/td][td]Lesson 1[/td][td]AP Euro History[/td][/tr][tr][td]2[/td][td]Lesson 1[/td][td]AP US History[/td][/tr][tr][td]2[/td][td]Fun quiz[/td][td][/td][/tr][/table] Quote Link to comment Share on other sites More sharing options...
the_924 Posted October 17, 2006 Author Share Posted October 17, 2006 Update:This is what I've gotten so far,[code]SELECT q.id, q.course_id, q.title, c.id, c.nameFROM ft_quiz q, ft_course cWHERE q.course_id = c.id[/code] But it leaves out the quizzes that aren't attatched to a course; still working on it :DEDIT:[table][tr][td][b]id [/b][/td][td][b]course_id [/b][/td][td][b]title [/b][/td][td][b]id [/b][/td][td][b]name [/b][/td][/tr][tr][td]1[/td][td]1[/td][td]AP US Quiz[/td][td]1[/td][td]US History (AP)[/td][/tr][tr][td]2[/td][td]0[/td][td]Quiz - No Course[/td][td]1[/td][td]US History (AP)[/td][/tr][tr][td]2[/td][td]0[/td][td]Quiz - No Course[/td][td]2[/td][td]Euro History (AP)[/td][/tr][/table] Quote Link to comment Share on other sites More sharing options...
fenway Posted October 17, 2006 Share Posted October 17, 2006 You need a LEFT JOIN:[code]SELECT q.id, q.course_id, q.title, c.id, c.nameFROM ft_quiz qLEFT JOIN ft_course c ON ( q.course_id = c.id )[/code] Quote Link to comment Share on other sites More sharing options...
the_924 Posted October 20, 2006 Author Share Posted October 20, 2006 Thank you :) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.