Jump to content

Archived

This topic is now archived and is closed to further replies.

the_924

Joins, subqueries, confusion

Recommended Posts

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:
[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]

Share this post


Link to post
Share on other sites
Update:
This is what I've gotten so far,
[code]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[/code]

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

EDIT:
[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]

Share this post


Link to post
Share on other sites
You need a LEFT JOIN:

[code]
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 )
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.