0

I have two tables with following structure: First Table:

ID    Name    Total_courses_allowed
1     Ahmad     4
2     Khan      3
3     Mahmood   2

Second Table:

ID   Course_attended   emp_id
1           f             1
2           t             1  
3           f             1
4           t             1
5           f             2
6           t             2
7           t             2
8           f             3
9           f             3
10          t             1
11          f             1
12          t             2
13          f             2

For each record in first table there should be as many records as their total_courses allowed in second table. but for some of the records it is created more than that their allowed number of courses to attend like for, ID 1 of first table 6 rows are generated in second table whereas it should be only 4.

I need a SQL query to find all the records which have more entries in the second table than their total allowed courses to attend.

Thanks,

  • I recommend you update your question to include the result set that you're expecting; also, what query(s) have you tried so far – markp Jun 11 '17 at 0:17
2

I'm sorry if it is not well formatted but I'm on mobile.

You can get it by counting attended courses and getting those records where total is lower.

select id, name, total_couses_allowed
from table1
inner join (select emp_id, count(*) as courses
            from table2
            where course_attended = 't'
            group by emp_id) t2
on table1id = t2.emp_id
where total_courses_allowed < courses
0
select id, name, Total_courses_allowed ,t2.real_Course_attended
from FirstTable t1
inner join (select emp_id, count(emp_id) as courses ,count(Course_attended) real_Course_attended
            from SecondTable
            where course_attended = 't'
            group by emp_id,Course_attended) t2
on t1.id = t2.emp_id
where t1.Total_courses_allowed >t2.courses;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.