multiple table query in mysql

Question

I am trying to query 4 tables(it works well when I query first 3 tables) and my table structure looks like,

1 Table - category

catid   catname
1       AAA
2       BBB

2 Table - questions

quid   qtype
1      data
2      
3      data

3 Table - answers

ansid   quid   catid   userid   answer
1       1      1       1        test1
2       2      1       1        test2
3       3      2       1        test3

4 Table - concerns

concern_id   catid   userid   ansid
1            1       1        1
2            1       4        3

Now my query is(with 3 tables)

SELECT category.catname,questions.quid,answers.catid,answers.ansid,answers.answer

FROM questions , answers ,category

where  questions.qtype = 'data' 

and questions.quid = answers.quid 

and category.catid = answers.catid 

and answers.userid = 1

And it gives me(which is fine)

catname   quid   catid   ansid   answer
AAA       1      1       1       test1
BBB       3      2       3       test3

Now I want to include the 4th table in the query and the resultant should look like

concern_id   catname   quid   catid   ansid   answer
1             AAA       1      1       1       test1
null          BBB       3      2       3       test3

And here I am stuck with the query.

Bit confused to use left join as there are 4 tables so not sure which one to keep as left table and which one to as right — Raj
– Raj, Commented Jul 24, 2014 at 13:30

Sashi Kant · Accepted Answer · 2014-07-24 13:30:59Z

1

Try this:

SELECT category.catname,questions.quid,answers.catid,answers.ansid,answers.answer

FROM 
questions 
INNER JOIN answers  on (questions.quid = answers.quid )
INNER JOIN category cat on (category.catid = answers.catid)
LEFT JOIN concerns con on (concerns.userId = answers.userId and concerns.catid = answers.catid  AND concerns.ansid = answers.ansid )
where  questions.qtype = 'data' 

and answers.userid = 1

answered Jul 24, 2014 at 13:30

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raj Over a year ago

Thanks Sashi...I modified the query a bit and worked like charm.I was doing left join on first 3 tables....what an idiot I was well still I am... :D Thanks for the help

Sashi Kant Over a year ago

Great that t helped :)

thewulf00 · Accepted Answer · 2014-07-24 13:33:22Z

1

Its not necessary to use joins. MySQL will compile the joined version to the same result as this version.

I think you should combine them by ansid.

SELECT concern.concern_id, category.catname, questions.quid, answers.catid, answers.ansid, answers.answer

FROM questions, answers, category, concerns

where questions.qtype = 'data' 

and concerns.ansid = answers.ansid

and questions.quid = answers.quid 

and category.catid = answers.catid 

and answers.userid = 1

answered Jul 24, 2014 at 13:33

thewulf00

673 bronze badges

1 Comment

thewulf00 Over a year ago

Describe the problem of the solution.

KidCode · Accepted Answer · 2014-07-24 13:40:01Z

1

You need to use joins:

SELECT category.catname,questions.quid,answers.catid,answers.ansid,answers.answer

FROM category

join answers 
on category.catid = answers.catid 

join questions
on questions.quid = answers.quid

join concern
on concerns.userId = answers.userId and concerns.catid = answers.catid  AND      concerns.ansid = answers.ansid

where  questions.qtype = 'data' 
and answers.userid = 1

edited Jul 24, 2014 at 13:40

answered Jul 24, 2014 at 13:31

KidCode

4,2614 gold badges22 silver badges38 bronze badges

1 Comment

KidCode Over a year ago

Forgot to include the last table :)

Collectives™ on Stack Overflow

multiple table query in mysql

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related