CODEWITHSUNDEEP
mysqlsql
Mandar Khire
Mandar Khire

Reputation: 39

Query for fetch two tables data combined

I have 2 tables. Table 1 name 'student' have columns like follows 

rowindex   roll_no  name
1          111      Peter
2          112      John

Table 2 name 'exam_dates' have columns like follows 

rowindex    roll_no    subject      date
1           111        Maths     2018-06-20
2           111        English   2018-06-21
3           112        Maths     2018-06-19
4           112        History   2018-06-22

Conditions for query as follows:-

Condition 1. Each student's Last exam date in 1 table by using those two tables.

&

Condition 2. If Exam date is less than today's date, then it should not come into the list.

I want to get result as

 1. Roll_no 111 have Maths at 2018-06-20 
 2. Roll_no 112 have History at 2018-06-22

For get this result what query I have to write? I tried query as follows:-

SELECT a.roll_no, a.name,b.subject, b.date 
FROM test_db.student a, test_db.exam_dates b 
Where a.roll_no = b.roll_no and (SELECT MAX(date) FROM exam_dates) 
group by a.roll_no 
order by a.roll_no, a.name,b.subject;

But No success. Need Help.

Upvotes: 0

Views: 57

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Condition 2. If Exam date is less than today's date, then it should not come into the list.

This is a WHERE condition.

Condition 1. Each student's Last exam date in 1 table by using those two tables.

This is MAX(date) per student.

You want to show the subject, too, so you'll get the max dates per student first and then query the exam_dates table again:

select s.roll_no, s.name, ed.subject, ed.date
from student s
join exam_dates ed on ed.roll_no = s.roll_no
where (ed.roll_no, ed.date) in
(
  select roll_no, max(date)
  from exam_dates
  where date >= current_date
  group by roll_no
);

Upvotes: 3

Related Questions