MySQL create view problem,

Question

Hello, I'm using a view as a table to join data on 3 tables:

create view category_list as 
select forum_categories.*, max( forum_answer.a_id ) as latest_answer_id 
from forum_categories 
left join forum_question on forum_question.catid = forum_categories.id 
left join forum_answer on forum_answer.question_id = forum_question.id
and forum_answer.qtitle = forum_question.topic;

However, I am experiencing two problems:

• Only one record from forum_categories is pulled out to be linked with
• I am unable to associate forum_answer.qtitle with forum_question.topic

please advise :)

Thanks.

Answer 1

The group by clause is missing. Here's a very simple example I put together, and below it the sql I used to create the simple test data.

select 
    cat.*
    , max( ans.id )
from cat
left join ques on ques.cat_id = cat.id
left join ans on ans.ques_id = ques.id
group by cat.id, cat.name

create table cat ( id serial, name varchar (10) );
create table ques ( id serial, name varchar (10), cat_id integer );
create table ans ( id serial, name varchar (10), ques_id integer );

insert into cat (name) values ( 'cat 1' );
insert into cat (name)  values ( 'cat 2' );
insert into cat  (name) values ( 'cat 3' );

insert into ques (name, cat_id) values ( 'ques 1', 1 );
insert into ques (name, cat_id)  values ( 'ques 2', 1 );
insert into ques (name, cat_id)  values ( 'ques 3', 1 );
insert into ques (name, cat_id)  values ( 'ques 4', 2 );
insert into ques (name, cat_id)  values ( 'ques 5', 2 );
insert into ques (name, cat_id)  values ( 'ques 6', 2 );
insert into ques (name, cat_id)  values ( 'ques 7', 2 );

insert into ans (name, ques_id)  values ( 'ans 1', 1 );
insert into ans (name, ques_id)  values ( 'ans 2', 2 );
insert into ans (name, ques_id)  values ( 'ans 3', 2 );
insert into ans (name, ques_id)  values ( 'ans 4', 1 );
insert into ans (name, ques_id)  values ( 'ans 5', 2 );
insert into ans (name, ques_id)  values ( 'ans 6', 1 );
insert into ans (name, ques_id)  values ( 'ans 7', 1 );
insert into ans (name, ques_id)  values ( 'ans 8', 1 );
insert into ans (name, ques_id)  values ( 'ans 9', 2 );
insert into ans (name, ques_id)  values ( 'ans 10', 2 );