CODEWITHSUNDEEP
mysql
Sateesh
Sateesh

Reputation: 31

Merging two columns in MYSQL

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..

Upvotes: 1

Views: 45

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 1

Related Questions