Jump to content


Photo

Joins, subqueries, confusion


  • Please log in to reply
3 replies to this topic

#1 the_924

the_924
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 16 October 2006 - 09:11 PM

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)
);

and

CREATE 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:

   



Quiz name    Quiz course
Lesson 1AP Euro History
Lesson 1AP US History
Fun quiz - NO COURSE! -


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:

       



quiz.id    quiz.name    course.name   
1Lesson 1AP Euro History
2Lesson 1AP US History
2Fun quiz


#2 the_924

the_924
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 17 October 2006 - 12:32 AM

Update:
This is what I've gotten so far,
SELECT q.id, q.course_id, q.title, c.id, c.name
FROM ft_quiz q, ft_course c
WHERE q.course_id = c.id


But it leaves out the quizzes that aren't attatched to a course; still working on it :D

EDIT:





id course_id    title    id    name   
11AP US Quiz1US History (AP)
20Quiz - No Course1US History (AP)
20Quiz - No Course2Euro History (AP)


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 03:47 PM

You need a LEFT JOIN:

SELECT q.id, q.course_id, q.title, c.id, c.name
FROM ft_quiz q
LEFT JOIN ft_course c ON ( q.course_id = c.id )

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 the_924

the_924
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 20 October 2006 - 04:39 AM

Thank you :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users