Merging two columns in MYSQL

Question

I'am working on attendance project. In that i am storing students who are absent on the particular date in the table 'attendance' with fields -> sno,rollno,subject_code,date. And if all the students are present then i will store subject_code, date and rollno as NULL which means'all students are present'

------|--------|------------|-----------|  
sno   |rollno  |subject_code|  date     |  
1     | 1234   |  a110      | 12-12-2012|
2     | 1235   |  a110      | 12-12-2012|
3     | 1235   |  a111      | 14-12-2012|

iam taking the students details from table 'students' with fields -> rollno,name

  |--------|-------|
  | rollno |  name |
  | 1234   |  xyz  |
  | 1235   |  abc  |
  | 1236   |  mno  |
  | 1237   |  qrs  |

Now I want to list the students attendance such that output should be like this for particular date assume list for the date "12-12-2012"

list of "12-12-2012" for subject a110   list of "14-12-2012" for subject a111
     |-------|------|---------|           |-------|------|---------|      
     |rollno | name | status  |           |rollno | name | status  |  
     | 1234  | xyz  | Absent  |           | 1234  | xyz  | Present |  
     | 1235  | abc  | Absent  |           | 1235  | abc  | Absent  |  
     | 1236  | mno  | Present |           | 1236  | mno  | Present |  
     | 1237  | qrs  | Present |           | 1237  | qrs  | Present |

Please Help me..

what happen if multiple student are absent on same day? are multiple rows on first table? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jan 12, 2016 at 15:55
students who are absent are added in the attendance table for that day and the subject — Sateesh
– Sateesh, Commented Jan 12, 2016 at 15:56
Can you update your sample to show that case? two student Absent on same day? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jan 12, 2016 at 15:57

Juan Carlos Oropeza · Accepted Answer · 2016-01-12 16:04:49Z

1

Use LEFT JOIN to find out which student doesnt have a row in the attendance table. (those where present)

 SELECT s.rollno,
        s.name, 
        CASE WHEN a.rollno IS NULL THEN 'Present'
             ELSE 'Absent'
        END as status
FROM student s
LEFT JOIN attendance a
       ON s.rollno = a.rollno

answered Jan 12, 2016 at 16:04

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

Collectives™ on Stack Overflow

Merging two columns in MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related