Geek Answers Handbook

MySQL gets different Bewteen Two Tables records

I need to show the "Scheduled vs Actual" report, which shows the difference between the two tables in the MySQL database that are attending school.

I have two tables called "Reservation and Visit."

The data stored in the tables is shown below:

Book now

Id Student   Date     IsAbsent
1  John      20160216    1 //NO
2  Bob       20160217    1 //NO
3  Zara      20160218    1 //NO

Members

Id Student    Date     IsAbsent
1  John       20160216    0 //YES
2  Bob        20160217    0 //YES
3  Mary       20160217    1 //NO

Basically, I want to show the output as

**Id | Student | Day_1 | Day_2  |  Day_3**
 ==== ========= ======  =======  ======
   1 | John    |ABSENT | NULL   | NULL 
   2 | Bob     |NULL   |ABSENT  | NULL
   3 | Mary    |NULL   |NEW     | NULL
   4 | Zara    |DELETED|NULL    | NULL

NO John in the reservation table is marked as 1 (False), but in the attendance table he is marked as 0 (YES), so I want to show it as "ABSENT"

NEW Mary only has an entry in the Attendance table, but not in the reservation table.

DELETED Zara was originally booked and is located in the reservation table, but is not in the attendance table.

SQL Fiddle , , null.

SQL- .

 SELECT * FROM 
((SELECT
  a.Student as student,
   MAX( case
       when a.DropDate='20160216' && a.IsAbsent=0 && s.IsAbsent=1 then 'Absent'
       when (select count(*) from attendance where DropDate='20160216')=0 && (select count(*) from staging where DropDate='20160216')>0 then 'DELETED Booking'
       when (select count(*) from attendance where DropDate='20160216')>0 && (select count(*) from staging where DropDate='20160216')=0 then 'New Booking' 
       else ' ' end ) as 'day_1',
  MAX( case 
       when a.DropDate='20160217' && a.IsAbsent=0 && s.IsAbsent=1 then 'Absent'
       when (select count(*) from attendance where DropDate='20160217')=0 && (select count(*) from staging where DropDate='20160217')>0 then 'DELETED Booking'
       when (select count(*) from attendance where DropDate='20160217')>0 && (select count(*) from staging where DropDate='20160217')=0 then 'New Booking' 
       else ' '  end ) as 'day_2',
  MAX( case 
       when a.DropDate='20160218' && a.IsAbsent=0 && s.IsAbsent=1 then 'Absent'
       when (select count(*) from attendance where DropDate='20160218')=0 && (select count(*) from staging where DropDate='20160218')>0 then 'DELETED Booking'
       when (select count(*) from attendance where DropDate='20160218')>0 && (select count(*) from staging where DropDate='20160218')=0 then 'DELETED Booking' 
       else ' '  end ) as 'day_3'
FROM Attendance a LEFT JOIN Booking s on a.Student=s.Student
WHERE a.DropDate IN ('20160216','20160217','20160218')
   AND  NOT EXISTS 
      ( SELECT 1
        FROM Booking AS p
        WHERE p.Student = a.Student
          AND p.IsAbsent = a.IsAbsent
          AND p.DropDate = a.DropDate
      )
)
UNION 
(SELECT
  t.Student as student,
  MAX( case
       when t.DropDate='20160216' && a.IsAbsent=0 && t.IsAbsent=1 then 'Absent'
       when (select count(*) from attendance where DropDate='20160216')=0 && (select count(*) from staging where DropDate='20160216')>0 then 'DELETED Booking'
       when (select count(*) from attendance where DropDate='20160216')>0 && (select count(*) from staging where DropDate='20160216')=0 then 'New Booking' 
       else ' ' end ) as 'day_1',
  MAX( case 
       when a.DropDate='20160217' && a.IsAbsent=0 && t.IsAbsent=1 then 'Absent'
       when (select count(*) from attendance where DropDate='20160217')=0 && (select count(*) from staging where DropDate='20160217')>0 then 'DELETED Booking'
       when (select count(*) from attendance where DropDate='20160217')>0 && (select count(*) from staging where DropDate='20160217')=0 then 'New Booking' 
       else ' '  end ) as 'day_2',
  MAX( case 
       when a.DropDate='20160218' && a.IsAbsent=0 && t.IsAbsent=1 then 'Absent'
       when (select count(*) from attendance where DropDate='20160218')=0 && (select count(*) from staging where DropDate='20160218')>0 then 'DELETED Booking'
       when (select count(*) from attendance where DropDate='20160218')>0 && (select count(*) from staging where DropDate='20160218')=0 then 'DELETED Booking' 
       else ' '  end ) as 'day_3'
FROM Booking t LEFT JOIN attendance a on t.Student=a.Student
WHERE t.DropDate IN ('20160216','20160217','20160218')
  AND  NOT EXISTS 
      ( SELECT 1
        FROM Attendance AS u
        WHERE u.Student = t.Student
          AND u.IsAbsent = t.IsAbsent
          AND u.DropDate = t.DropDate
      )
)) tbl
ORDER BY  student

.

+4
1

Im ' , MySQL-, ... , , , :

(select s.Student,
       s.ClassRoom,
       s.DropDate,
       if(s.IsAbsent=1&&a.IsAbsent=0,'ABSENT','PRESENT')
from Staging s inner join Attendance a on a.Student = s.Student and a.ClassRoom = s.ClassRoom and a.DropDate = s.DropDate)
UNION ALL
(Select s.Student,
       s.ClassRoom,
       s.DropDate,
       'DELETED'
from Staging s left join Attendance a on a.Student = s.Student and a.ClassRoom = s.ClassRoom and a.DropDate = s.DropDate
where a.Student is null)
UNION ALL
(Select a.Student,
       a.ClassRoom,
       a.DropDate,
       'NEW'
from Staging s right join Attendance a on a.Student = s.Student and a.ClassRoom = s.ClassRoom and a.DropDate = s.DropDate
where s.Student is null)
order by Student, DropDate;
+2

More articles:


All Articles