Combine data in 3 tables when data is missing in 3rd table using MySQL

Question

I am stuck with a scenario in mySQL : I have 3 tables,
ClassRoll:

roll_no | name
123     | jack
456     | jill
789     | miriam

StudMarks:

name | Subject | marks
jack | math    | 15 
jill | science | 12

ExtraCurricularActivity:

name  | activity
jill  | singing
miriam| dancing

I need a query which will result : StudentSummary as

name  | subject | marks | activity
jack  | math    | 15    | NA
jill  | science | 12    | singing
miriam| NA      | NA    | dancing

I tried different ways using joins but i am unable to obtain a desired result.

Show us what you tried - (hint: you need an OUTER JOIN for this) — Strawberry
– Strawberry, Commented Aug 29, 2014 at 10:32
I would go about reviewing your schema. Using names as a unique identifier between tables is not good practise — SEB BINFIELD
– SEB BINFIELD, Commented Aug 29, 2014 at 10:34
@Strawberry: 1. SELECT cr.name, sm.marks, sm.subject, eca.activity FROM ClassRoll cr LEFT JOIN StudMarks sm ON sm.name = cr.name, extracurricularactivity eca WHERE eca.name = cr.name; 2. SELECT cr.name, (CASE WHEN sm.name=cr.name THEN sm.marks ELSE 'NA' END ) marks, (CASE WHEN sm.name=cr.name THEN sm.subject ELSE 'NA' END ) subject, (CASE WHEN eca.name=cr.name THEN eca.activity ELSE 'NA' END ) activity FROM ClassRoll cr, StudMarks sm, extracurricularactivity eca WHERE cr.name=sm.name AND cr.name=eca.name; — nam
– nam, Commented Aug 29, 2014 at 15:00
@SEBBINFIELD You are right about it. But since I do not have write access to the db i'm trying to find a soln with the current scenario. — nam
– nam, Commented Aug 29, 2014 at 15:07

Ende Neu · Accepted Answer · 2014-08-29 10:32:23Z

2

Use LEFT JOIN (or right depending on the sequence):

SELECT
  r.name,
  s.marks,
  s.subject,
  e.activity
FROM ClassRoll r
LEFT JOIN StudMarks s
  ON s.name = r.name
LEFT JOIN ExtraCurricularActivity e
ON e.name = r.name

answered Aug 29, 2014 at 10:32

Ende Neu

15.8k5 gold badges61 silver badges72 bronze badges

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

2 Comments

nam Over a year ago

Thank you. This worked perfectly. I neva thought of applying the left join on the third table instead was trying with WHERE clause. (Messing it up even more)

Ende Neu Over a year ago

You're welcome, take some time to study the join documentation, it's one of the fundamental concept in sql.

Jens · Accepted Answer · 2014-08-29 10:32:38Z

0

this should give you the expected result:

select t1.name,t1.subject, case when t1.makrs is null then "NA" else t1.marks end as marks, t2.activity from StudMarks t1 left join ExtraCurricularActivity t2 on  t1.name=t2.name

answered Aug 29, 2014 at 10:32

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Comments

alez007 · Accepted Answer · 2014-08-29 10:32:50Z

0

SELECT c.name, s.subject, s.marks, e.activity FROM ClassRoll c LEFT JOIN StudMarks s ON c.name = s.name LEFT JOIN ExtraCurricularActivity e ON c.name = e.name

answered Aug 29, 2014 at 10:32

alez007

2761 silver badge6 bronze badges

Collectives™ on Stack Overflow

Combine data in 3 tables when data is missing in 3rd table using MySQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related